列出 PostgreSQL 中模式的所有非生成序列

Question

在 PostgreSQL 中，有两种类型的序列：

• 用户使用 CREATE SEQUENCE 创建的序列
• 由数据库生成的序列，用于支持 SERIAL

INFORMATION_SCHEMA 类型的列。SEQUENCES 返回两种类型的序列。

什么SQL语句可以获取特定模式的用户创建的序列列表（不包括数据库生成的序列）？

PS：我使用的是 PostgreSQL 9

Answer 1

对于生成的序列，将自动定义“拥有”列，以便可以成为区分因素。

但这也可以手动完成，因此无法区分：

create table foo (
   id_col serial not null
);

和

create table foo (
   id_col integer not null
);
create sequence foo_id_col_seq owned by foo.id_col;

但是，如果这对您来说没问题，则以下语句可以为您提供该信息：

SELECT s.relname as sequence_name,  
       t.relname as related_table, 
       a.attname as related_column,
       d.deptype
FROM pg_class s 
   JOIN pg_depend d ON d.objid = s.oid 
   LEFT JOIN pg_class t ON d.refobjid = t.oid 
   LEFT JOIN pg_attribute a ON (d.refobjid, d.refobjsubid) = (a.attrelid, a.attnum) 
WHERE s.relkind = 'S';

您将通过该查询获得每个序列的至少一行：对创建该序列的架构的依赖关系。

对于由您所拥有的列所拥有的序列将得到另一行及其所属的表和列。

Answer 2

唯一的区别可能（至少）在于它的定义位置。输入 \d yourtable 会将修饰符列中的序列列为 nextval(...)，但随后定义的序列则不会。 pg_catalog.attr_def 将数据保存到用于定义的实际字符串中。

我同意马的观点，即功能上没有区别。它类似于在一两行中声明某些内容，但会发生完全相同的事情。 数据库正在生成所有这些序列，并且没有用户序列。唯一的区别在于它们的声明方式以及保留此信息的唯一方式是定义列的实际字符串保存在系统目录中。

来自系统表：

create view check_seq as
SELECT a.attname,
    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 "column def"

FROM pg_catalog.pg_attribute a
join pg_catalog.pg_class c on a.attrelid = c.oid
WHERE a.attnum > 0 AND NOT a.attisdropped
and c.relname in ('foo')
ORDER BY a.attnum
;

示例：

create table foo (
   id_col serial not null
);
select * from check_seq;

 attname | relname |             column def           
 ---------+---------+-------------------------------------
  id_col  | foo     | nextval('foo_id_col_seq'::regclass)


drop table foo;
create table foo (
   id_col integer not null
);
create sequence foo_id_col_seq owned by foo.id_col;
select * from check_seq;

 attname | relname | column def
 ---------+---------+------------
  id_col  | foo     |