我从Postgres的转换分贝到MySQL。
因为我无法找到一个工具，做的伎俩本身，我要去把所有Postgres的序列自动增量IDS与自动增量值的MySQL。
所以，我怎么能列出一个Postgres数据库（8.1版本）有关的表信息的所有序列中，它的使用，下一个值等与SQL查询？
要知道，我不能使用information_schema.sequences在8.4版本视图。
Answer 1:
下面的查询提供了所有序列的名字。
SELECT c.relname FROM pg_class c WHERE c.relkind = 'S';
通常，一个序列被命名为${table}_id_seq 。 简单的regex模式匹配会给你的表名。
为了得到一个序列的最后一个值使用以下查询：
SELECT last_value FROM test_id_seq;
Answer 2:
请注意，从的PostgreSQL 8.4开始，你可以获取有关通过数据库使用的序列的所有信息：
SELECT * FROM information_schema.sequences;
由于我使用的是更高版本的PostgreSQL（9.1），并正在寻找相同的答案高低，我说这个答案留给后人的缘故，并为未来的搜索。
Answer 3:
兼营： psql -E ，然后\ds
Answer 4:
疼痛一点点后，我知道了。
实现这一目标的最佳方法是列出所有表
select * from pg_tables where schemaname = '<schema_name>'
然后，对每个表，列出具有属性的所有列
select * from information_schema.columns where table_name = '<table_name>'
然后，对于每一列，测试，如果它有一个序列
select pg_get_serial_sequence('<table_name>', '<column_name>')
然后，获取有关该序列的信息
select * from <sequence_name>
Answer 5:
自动生成的序列（例如用于SERIAL列创建）和父表之间的关系是由序列所有者属性建模。
您可以使用所拥有的条款修改此关系ALTER SEQUENCE条命令
例如，ALTER SEQUENCE foo_id通过foo_schema.foo_table国有
将其设置为链接表foo_table
或ALTER SEQUENCE foo_id由NONE国有
打破序列和任何表之间的连接
这种关系的信息存储在pg_depend目录表 。
接合关系是pg_depend.objid之间的链路 - > WHERE pg_class.oid = relkind“S” - 一个把该序列与加入记录，然后pg_depend.refobjid - > WHERE pg_class.oid = relkind“R”，一个把该加入记录所属关系（表）
该查询返回所有序列 - >表依赖于数据库中。 where子句它过滤为仅包括自动生成的关系，这会限制由SERIAL类型列创建仅显示序列。
WITH fq_objects AS (SELECT c.oid,n.nspname || '.' ||c.relname AS fqname ,
c.relkind, c.relname AS relation
FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace ),
sequences AS (SELECT oid,fqname FROM fq_objects WHERE relkind = 'S'),
tables AS (SELECT oid, fqname FROM fq_objects WHERE relkind = 'r' )
SELECT
s.fqname AS sequence,
'->' as depends,
t.fqname AS table
FROM
pg_depend d JOIN sequences s ON s.oid = d.objid
JOIN tables t ON t.oid = d.refobjid
WHERE
d.deptype = 'a' ;
Answer 6:
序列信息：最大值
SELECT * FROM information_schema.sequences;
序列信息：最后一个值
SELECT * FROM <sequence_name>
Answer 7:
我知道这个职位是很老，但我发现通过该解决方案CMS是非常有用的，因为我一直在寻找一种自动化的方式来序列链接到表和列，并希望分享。 采用pg_depend目录表是关键。 我扩大了什么做的目的是：
WITH fq_objects AS (SELECT c.oid,n.nspname || '.' ||c.relname AS fqname ,
c.relkind, c.relname AS relation
FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace ),
sequences AS (SELECT oid,fqname FROM fq_objects WHERE relkind = 'S'),
tables AS (SELECT oid, fqname FROM fq_objects WHERE relkind = 'r' )
SELECT
s.fqname AS sequence,
'->' as depends,
t.fqname AS table,
a.attname AS column
FROM
pg_depend d JOIN sequences s ON s.oid = d.objid
JOIN tables t ON t.oid = d.refobjid
JOIN pg_attribute a ON a.attrelid = d.refobjid and a.attnum = d.refobjsubid
WHERE
d.deptype = 'a' ;
该版本增加了列返回的字段列表。 与这两个表名在手，列名，将呼叫pg_set_serial_sequence可以很容易地保证数据库中的所有序列的设置是否正确。 例如：
CREATE OR REPLACE FUNCTION public.reset_sequence(tablename text, columnname text)
RETURNS void
LANGUAGE plpgsql
AS $function$
DECLARE
_sql VARCHAR := '';
BEGIN
_sql := $$SELECT setval( pg_get_serial_sequence('$$ || tablename || $$', '$$ || columnname || $$'), (SELECT COALESCE(MAX($$ || columnname || $$),1) FROM $$ || tablename || $$), true)$$;
EXECUTE _sql;
END;
$function$;
希望这可以帮助别人与复位序列！
Answer 8:
部分测试，但看起来基本完成。
select *
from (select n.nspname,c.relname,
(select substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)
from pg_catalog.pg_attrdef d
where d.adrelid=a.attrelid
and d.adnum=a.attnum
and a.atthasdef) as def
from pg_class c, pg_attribute a, pg_namespace n
where c.relkind='r'
and c.oid=a.attrelid
and n.oid=c.relnamespace
and a.atthasdef
and a.atttypid=20) x
where x.def ~ '^nextval'
order by nspname,relname;
信贷，信用是由于......它是部分逆转从一个\ d登录一个已知的表有一个序列中的SQL工程。 我敢肯定，这可能是更清洁过，但是，嘿，性能是不是一个问题。
Answer 9:
一个黑客的那种，而是试试这个：
选择“选择‘’” || relname || “”“如序列，从LAST_VALUE” || relname || '联盟' FROM pg_catalog.pg_class c其中c.relkind IN（ 'S'， ''）;
删除最后UNION和执行结果
Answer 10:
以前的答案的改进：
select string_agg('select sequence_name, last_value from ' || relname, chr(13) || 'union' || chr(13) order by relname)
from pg_class where relkind ='S'
Answer 11:
该声明列出了与每个序列相关联的表和列：
码：
SELECT t.relname as related_table,
a.attname as related_column,
s.relname as sequence_name
FROM pg_class s
JOIN pg_depend d ON d.objid = s.oid
JOIN pg_class t ON d.objid = s.oid AND d.refobjid = t.oid
JOIN pg_attribute a ON (d.refobjid, d.refobjsubid) = (a.attrelid, a.attnum)
JOIN pg_namespace n ON n.oid = s.relnamespace
WHERE s.relkind = 'S'
AND n.nspname = 'public'
更看到这里链接到答案
Answer 12:
谢谢你的帮助。
这里是PL / pgSQL函数哪个更新数据库的每个序列。
---------------------------------------------------------------------------------------------------------
--- Nom : reset_sequence
--- Description : Générique - met à jour les séquences au max de l'identifiant
---------------------------------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION reset_sequence() RETURNS void AS
$BODY$
DECLARE _sql VARCHAR := '';
DECLARE result threecol%rowtype;
BEGIN
FOR result IN
WITH fq_objects AS (SELECT c.oid,n.nspname || '.' ||c.relname AS fqname ,c.relkind, c.relname AS relation FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace ),
sequences AS (SELECT oid,fqname FROM fq_objects WHERE relkind = 'S'),
tables AS (SELECT oid, fqname FROM fq_objects WHERE relkind = 'r' )
SELECT
s.fqname AS sequence,
t.fqname AS table,
a.attname AS column
FROM
pg_depend d JOIN sequences s ON s.oid = d.objid
JOIN tables t ON t.oid = d.refobjid
JOIN pg_attribute a ON a.attrelid = d.refobjid and a.attnum = d.refobjsubid
WHERE
d.deptype = 'a'
LOOP
EXECUTE 'SELECT setval('''||result.col1||''', COALESCE((SELECT MAX('||result.col3||')+1 FROM '||result.col2||'), 1), false);';
END LOOP;
END;$BODY$ LANGUAGE plpgsql;
SELECT * FROM reset_sequence();
Answer 13:
这里是另外一个，具有序列名称旁边的架构名称
select nspname,relname from pg_class c join pg_namespace n on c.relnamespace=n.oid where relkind = 'S' order by nspname
Answer 14:
通过DEFAULT子句的解析得到由每个表的每列的序列。 该方法提供关于哪个列序列被连接 ，并且不使用信息 的依赖关系 ，其可能不适用于某些序列存在。 即使pg_get_serial_sequence(sch.nspname||'.'||tbl.relname, col.attname)函数发现并不是所有的序列我！
解：
SELECT
seq_sch.nspname AS sequence_schema
, seq.relname AS sequence_name
, seq_use."schema" AS used_in_schema
, seq_use."table" AS used_in_table
, seq_use."column" AS used_in_column
FROM pg_class seq
INNER JOIN pg_namespace seq_sch ON seq_sch.oid = seq.relnamespace
LEFT JOIN (
SELECT
sch.nspname AS "schema"
, tbl.relname AS "table"
, col.attname AS "column"
, regexp_split_to_array(
TRIM(LEADING 'nextval(''' FROM
TRIM(TRAILING '''::regclass)' FROM
pg_get_expr(def.adbin, tbl.oid, TRUE)
)
)
, '\.'
) AS column_sequence
FROM pg_class tbl --the table
INNER JOIN pg_namespace sch ON sch.oid = tbl.relnamespace
--schema
INNER JOIN pg_attribute col ON col.attrelid = tbl.oid
--columns
INNER JOIN pg_attrdef def ON (def.adrelid = tbl.oid AND def.adnum = col.attnum) --default values for columns
WHERE tbl.relkind = 'r' --regular relations (tables) only
AND col.attnum > 0 --regular columns only
AND def.adsrc LIKE 'nextval(%)' --sequences only
) seq_use ON (seq_use.column_sequence [1] = seq_sch.nspname AND seq_use.column_sequence [2] = seq.relname)
WHERE seq.relkind = 'S' --sequences only
ORDER BY sequence_schema, sequence_name;
需要注意的是1个序列可以在多个表中使用，因此它可以在多行这里上市。
Answer 15:
此功能显示每个序列的LAST_VALUE。
它输出一个2列的表，上面写着序列名称，再加上它的最后产生的价值。
drop function if exists public.show_sequence_stats();
CREATE OR REPLACE FUNCTION public.show_sequence_stats()
RETURNS TABLE(tablename text, last_value bigint)
LANGUAGE 'plpgsql'
COST 100
VOLATILE
ROWS 1000
AS $BODY$
declare r refcursor; rec record; dynamic_query varchar;
BEGIN
dynamic_query='select tablename,last_value from (';
open r for execute 'select nspname,relname from pg_class c join pg_namespace n on c.relnamespace=n.oid where relkind = ''S'' order by nspname';
fetch next from r into rec;
while found
loop
dynamic_query=dynamic_query || 'select '''|| rec.nspname || '.' || rec.relname ||''' "tablename",last_value from ' || rec.nspname || '.' || rec.relname || ' union all ';
fetch next from r into rec;
end loop;
close r;
dynamic_query=rtrim(dynamic_query,'union all') || ') x order by last_value desc;';
return query execute dynamic_query;
END;
$BODY$;
select * from show_sequence_stats();
Answer 16:
假设exec()在这篇文章中声明的函数https://stackoverflow.com/a/46721603/653539 ，具有其最后的值一起序列可以使用单个查询被取出：
select s.sequence_schema, s.sequence_name,
(select * from exec('select last_value from ' || s.sequence_schema || '.' || s.sequence_name) as e(lv bigint)) last_value
from information_schema.sequences s
文章来源: List all sequences in a Postgres db 8.1 with SQL
