information_schema.sequences视图。SELECT c.relname FROM pg_class c WHERE c.relkind = 'S' order BY c.relname;
${table}_id_seq。简单的正则表达式模式匹配将给出表名。SELECT last_value FROM test_id_seq;
${table}_id_seq 提示很有用。 - Pierre de LESPINAY${table}_${column}_seq表示自动生成的序列。 - Evgeny Nozdrev请注意，从PostgreSQL 8.4开始，您可以通过以下方式获取数据库中使用的所有序列的信息：
SELECT * FROM information_schema.sequences;
由于我正在使用较高版本的PostgreSQL（9.1），并且一直在寻找相同的答案，因此我为了后人和未来的搜索者添加了这个答案。
SELECT last_value FROM <sequence_name> 来获取它。这个答案展示了序列的模式，这正是我需要过滤的内容，而且在 pg_class 视图中是不可用的。 - GameSalutes使用-E标志（“回显由\d和其他反斜杠命令生成的实际查询”）启动psql，然后输入\ds命令以列出所有序列。您应该会看到类似于以下内容：
# \ds
********* QUERY **********
SELECT n.nspname as "Schema",
c.relname as "Name",
CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' WHEN 'p' THEN 'partitioned table' WHEN 'I' THEN 'partitioned index' END as "Type",
pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('S','')
AND n.nspname <> 'pg_catalog'
AND n.nspname <> 'information_schema'
AND n.nspname !~ '^pg_toast'
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
**************************
List of relations
Schema | Name | Type | Owner
--------+--------------------------------+----------+-------
public | assignments_id_seq | sequence | root
public | framework_users_id_seq | sequence | root
public | lending_items_id_seq | sequence | root
public | proxy_borrower_requests_id_seq | sequence | root
public | roles_id_seq | sequence | root
public | stack_requests_id_seq | sequence | root
(6 rows)
要检查特定的序列，您可以运行\d <序列名称>：
# \d lending_items_id_seq
********* QUERY **********
(...about four queries...)
**************************
Sequence "public.lending_items_id_seq"
Type | Start | Minimum | Maximum | Increment | Cycles? | Cache
--------+-------+---------+---------------------+-----------+---------+-------
bigint | 1 | 1 | 9223372036854775807 | 1 | no | 1
Owned by: public.lending_items.id
经过一番努力，我终于明白了。
实现这个目标的最好方法是列出所有的表格。
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>
序列信息：最大值
SELECT * FROM information_schema.sequences;
序列信息：上次值
SELECT * FROM <sequence_name>
获取所有序列：
select * from pg_sequences;
PSQL：
\ds
\ds+
\ds *actor*
\ds *actor*将获取所有序列名称中包含actor字符的序列。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' ;
我知道这篇文章很旧了，但是我发现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$;
我知道这个问题是关于PostgreSQL 8版本的，但我在这里写了一个简单的方法，供想要获得版本10及以上序列的人使用
您可以使用以下查询
select * from pg_sequences
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'
更多见此处答案链接