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

Question

在PostgreSQL中有两种序列类型：

• 由用户使用CREATE SEQUENCE创建的序列
• 由数据库生成以支持类型为SERIAL的列

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

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

P.S .：我正在使用PostgreSQL 9

- Axel Fontaine

Answer 1

对于生成的序列，"拥有"列将自动定义，以便成为一个可以区分的因素。但也可以手动完成此操作，因此无法区分以下两种情况：

create table foo (
   id_col serial not null
);

and

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';

该查询将为每个序列至少返回一行：它所依赖的模式。

对于由列拥有的序列，您将获得另一行，其中包含它所属的表格和列。

- user330315

---

pg_catalog.attr_def 保存了原始列定义。\d 命令只会列出第一个带有 'nextval' 修饰符的 foo，而第二个则只有 'not null'。 - nate c

Answer 2

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

我同意horse的观点，在功能上没有区别。这就像在一行或两行上声明某些内容，但是发生的确切事情完全相同。数据库正在生成所有这些序列，并且没有用户序列。唯一的区别在于它们的声明方式，而此信息持久化的唯一方式是系统目录中保存定义列的实际字符串。

从系统表中：

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     |

- nate c