使用 SQL 列出 Postgres db 8.1 中的所有序列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1493262/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
List all sequences in a Postgres db 8.1 with SQL
提问by apelliciari
I'm converting a db from postgres to mysql.
我正在将数据库从 postgres 转换为 mysql。
Since i cannot find a tool that does the trick itself, i'm going to convert all postgres sequences to autoincrement ids in mysql with autoincrement value.
由于我找不到可以自行完成此操作的工具，因此我将使用自动增量值将所有 postgres 序列转换为 mysql 中的自动增量 ID。
So, how can i list all sequences in a PostgresDB (8.1version) with information about the table in which it's used, the next value etc with a SQL query?
那么，如何使用 SQL 查询列出PostgresDB（8.1版本）中的所有序列以及有关使用它的表的信息、下一个值等？
Be aware that i can't use the information_schema.sequencesview in the 8.4 release.
请注意，我无法information_schema.sequences在 8.4 版本中使用该视图。
回答by Anand Chitipothu
The following query gives names of all sequences.
以下查询给出了所有序列的名称。
SELECT c.relname FROM pg_class c WHERE c.relkind = 'S';
Typically a sequence is named as ${table}_id_seq. Simple regex pattern matching will give you the table name.
通常一个序列被命名为${table}_id_seq. 简单的正则表达式模式匹配会给你表名。
To get last value of a sequence use the following query:
要获取序列的最后一个值，请使用以下查询：
SELECT last_value FROM test_id_seq;
回答by raveren
Note, that starting from PostgreSQL 8.4 you can get allinformation about sequences used in the database via:
请注意，从 PostgreSQL 8.4 开始，您可以通过以下方式获取有关数据库中使用的序列的所有信息：
SELECT * FROM information_schema.sequences;
Since I'm using a higher version of PostgreSQL (9.1), and was searching for same answer high and low, I added this answer for posterity's sake and for future searchers.
由于我使用的是更高版本的 PostgreSQL (9.1)，并且一直在寻找相同的答案，为了后代和未来的搜索者，我添加了这个答案。
回答by raveren
Run: psql -E, and then \ds
运行：psql -E，然后\ds
回答by apelliciari
after a little bit of pain, i got it.
经过一点点痛苦，我明白了。
the best way to achieve this is to list all tables
实现此目的的最佳方法是列出所有表
select * from pg_tables where schemaname = '<schema_name>'
and then, for each table, list all columns with attributes
然后，对于每个表，列出具有属性的所有列
select * from information_schema.columns where table_name = '<table_name>'
then, for each column, test if it has a sequence
然后，对于每一列，测试它是否有序列
select pg_get_serial_sequence('<table_name>', '<column_name>')
and then, get the information about this sequence
然后，获取有关此序列的信息
select * from <sequence_name>
回答by bbh
sequence info : max value
序列信息：最大值
SELECT * FROM information_schema.sequences;
SELECT * FROM information_schema.sequences;
sequence info : last value
序列信息：最后一个值
SELECT * FROM <sequence_name>
SELECT * FROM <sequence_name>
回答by cms
The relationship between automatically generated sequences ( such as those created for SERIAL columns ) and the parent table is modelled by the sequence owner attribute.
自动生成的序列（例如为 SERIAL 列创建的序列）和父表之间的关系由序列所有者属性建模。
You can modify this relationship using the OWNED BY clause of the ALTER SEQUENCE commmand
您可以使用ALTER SEQUENCE 命令的 OWNED BY 子句修改此关系
e.g. ALTER SEQUENCE foo_id OWNED by foo_schema.foo_table
例如 ALTER SEQUENCE foo_id 由 foo_schema.foo_table 拥有
to set it to be linked to the table foo_table
将其设置为链接到表 foo_table
or ALTER SEQUENCE foo_id OWNED by NONE
或 ALTER SEQUENCE foo_id 由 NONE 拥有
to break the connection between the sequence and any table
断开序列和任何表之间的连接
The information about this relationship is stored in the pg_depend catalogue table.
有关此关系的信息存储在pg_depend 目录表中。
the joining relationship is the link between pg_depend.objid -> pg_class.oid WHERE relkind = 'S' - which links the sequence to the join record and then pg_depend.refobjid -> pg_class.oid WHERE relkind = 'r' , which links the join record to the owning relation ( table )
连接关系是 pg_depend.objid -> pg_class.oid WHERE relkind = 'S' - 将序列链接到连接记录，然后 pg_depend.refobjid -> pg_class.oid WHERE relkind = 'r' 之间的链接，它链接将记录连接到拥有关系（表）
This query returns all the sequence -> table dependencies in a database. The where clause filters it to only include auto generated relationships, which restricts it to only display sequences created by SERIAL typed columns.
此查询返回数据库中的所有序列 -> 表依赖项。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' ;
回答by DBAYoder
I know this post is pretty old, but I found the solution by CMSto be very useful as I was looking for an automated way to link a sequence to the table AND column, and wanted to share. The use of pg_dependcatalog table was the key. I expanded what was done to:
我知道这篇文章已经很老了，但我发现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' ;
This version adds column to the list of fields returned. With both the table name and the column name in hand, a call to pg_set_serial_sequencemakes it easy to ensure that all sequences in the database are set correctly. For example:
此版本将列添加到返回的字段列表中。有了表名和列名，调用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$;
Hope this helps someone with resetting sequences!
希望这有助于重置序列的人！
回答by DBAYoder
This statement lists the table and column that is associated with each sequence:
此语句列出与每个序列关联的表和列：
Code:
代码：
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'
more see here link to answer
更多请看这里链接回答
回答by Alexander Ryabov
Improvement of the previous answer:
上一个答案的改进：
select string_agg('select sequence_name, last_value from ' || relname, chr(13) || 'union' || chr(13) order by relname)
from pg_class where relkind ='S'
回答by Alexander Ryabov
Partially tested but looks mostly complete.
经过部分测试，但看起来基本完整。
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;
Credit where credit is due... it's partly reverse engineered from the SQL logged from a \d on a known table that had a sequence. I'm sure it could be cleaner too, but hey, performance wasn't a concern.
信用到期的信用......它部分地从具有序列的已知表上的 \d 记录的 SQL 中逆向工程。我相信它也可以更干净，但是嘿，性能不是问题。
