列出使用 SQL 的 Postgres 数据库 8.1 中的所有序列

使用标志启动（“echo the actual queries generated by and other backslash command”），然后输入命令列出所有序列。您应该看到如下内容：psql-E\d\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 <sequence name>

# \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 (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 进行逆向工程的。我相信它也可以更干净，但是嘿，性能不是问题。

以下查询提供所有序列的名称。

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;

自动生成的序列（例如为 SERIAL 列创建的序列）与父表之间的关系由序列所有者属性建模。

您可以使用 ALTER SEQUENCE 通信的 OWNED BY 子句修改此关系

例如： ALTER SEQUENCE foo_id 归 foo_schema.foo_table 所有

将其设置为链接到表foo_table

或 ALTER SEQUENCE foo_id OWNED by NONE

断开序列与任何表之间的连接

有关此关系的信息存储在pg_depend目录表中。

连接关系是 pg_depend.objid -> pg_class.oid WHERE relkind = 'S' - 将序列链接到连接记录，然后 pg_depend.refobjid -> pg_class.oid WHERE 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' ;

有点黑客，但试试这个：

select 'select ''' || relname  || ''' as sequence, last_value from '  || relname || '  union'
FROM pg_catalog.pg_class c
WHERE c.relkind IN ('S','');

删除最后一个 UNION 并执行结果

对上一个答案的改进：

select string_agg('select sequence_name, last_value from ' || relname, chr(13) || 'union' || chr(13) order by relname) 
from pg_class where relkind ='S'

请注意，从 PostgreSQL 8.4 开始，您可以通过以下方式获取有关数据库中使用的序列的所有信息：

SELECT * FROM information_schema.sequences;

---

由于我使用的是更高版本的 PostgreSQL （9.1），并且一直在搜索相同的答案，因此为了后代和未来的搜索者，我添加了这个答案。

我知道这篇文章很旧，但我发现CMS的解决方案非常有用，因为我正在寻找一种将序列链接到表AND列的自动化方法，并想分享。使用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$;

希望这对重置序列的人有所帮助！

此语句列出与每个序列关联的表和列：

法典：

    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'

更多 请看这里 链接回答

感谢您的帮助。

这是 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();

序列信息 ： 最大值

SELECT * FROM information_schema.sequences;

序列信息：最后一个值

SELECT * FROM <sequence_name>

这是另一个在序列名称旁边具有架构名称的

select nspname,relname from pg_class c join pg_namespace n on c.relnamespace=n.oid where relkind = 'S' order by nspname

通过解析 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 个序列可以在多个表中使用，因此可以在此处的多行中列出。

此函数显示每个序列的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();

假设本文 https://stackoverflow.com/a/46721603/653539 中声明的函数，可以使用单个查询获取序列及其最后值：exec()

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

select sequence_name, (xpath('/row/last_value/text()', xml_count))[1]::text::int as last_value
from (
    select sequence_schema,
            sequence_name,         
            query_to_xml(format('select last_value from %I.%I', sequence_schema, sequence_name), false, true, '') as xml_count
    from information_schema.sequences
    where sequence_schema = 'public'
) new_table order by last_value desc;

下面是一个示例，如何使用它们来获取所有序列的列表：psqllast_value

psql -U <username> -d <database> -t -c "SELECT 'SELECT ''' || c.relname || ''' as sequence_name, last_value FROM ' || c.relname || ';' FROM pg_class c WHERE (c.relkind = 'S')" | psql -U <username> -d <database> -t

我知道这个问题是关于 postgresql 版本 8 的，但我在这里为想要获取版本 10 及更高版本的序列的人编写了这种简单的方法

您可以使用 BELLOW 查询

select * from pg_sequences

视图-pg-序列

获取所有序列：

select * from pg_sequences;

PSQL格式：

\ds
\ds+
\ds *actor*

\ds *actor*将获取序列名称包含 actor 字符的所有序列。

SELECT nextval(sequence_schema || '.' || sequence_name)
FROM information_schema.sequences