主页 / dba / 问题 / 47098
Accepted
Joishi Bodio
Asked: 2013-07-27 11:39:03 +0800 CST

如何根据目录确定列是否定义为串行数据类型而不是整数？

  • 772

所以我目前正在创建一些 SQL 来读取 postgres (9.1) 目录以构建表定义。但是，我遇到了 SERIAL/BIGSERIAL 数据类型的问题。

例子：

CREATE TABLE cruft.temp ( id BIGSERIAL PRIMARY KEY );
SELECT * FROM information_schema.columns WHERE table_schema='cruft' AND table_name='temp';
"db","cruft","temp","id",1,"nextval('cruft.temp_id_seq'::regclass)","NO","bigint",,,64,2,0,,,,,,,,,,,,,"db","pg_catalog","int8",,,,,"1","NO","NO",,,,,,,"NEVER",,"YES"

它给了我数据库名称 (db)、模式名称 (cruft)、表名称 (temp)、列名称 (id)、默认值 (nextval( ... )) 和数据类型 (bigint 和 int8 .. NOT bigserial) ...我意识到我可以检查默认值是否是一个序列 - 但我不相信这会 100% 准确，因为我可以手动创建一个序列并创建一个默认值为的非序列列那个序列。

有没有人对我如何做到这一点有建议？除了检查 nextval(*_seq) 的默认值之外还有什么？

在 TL;DR 或不熟悉 pg_catalog 的新用户的情况下，针对此处添加的 SQL 解决方案进行了编辑：

with sequences as (
  select oid, relname as sequencename from pg_class where relkind = 'S'
) select
  sch.nspname as schemaname, tab.relname as tablename, col.attname as columnname, col.attnum as columnnumber, seqs.sequencename
from pg_attribute col
join pg_class tab on col.attrelid = tab.oid
join pg_namespace sch on tab.relnamespace = sch.oid
left join pg_attrdef def on tab.oid = def.adrelid and col.attnum = def.adnum
left join pg_depend deps on def.oid = deps.objid and deps.deptype = 'n'
left join sequences seqs on deps.refobjid = seqs.oid
where sch.nspname != 'information_schema' and sch.nspname not like 'pg_%' -- won't work if you have user schemas matching pg_
  and col.attnum > 0
  and seqs.sequencename is not null -- TO ONLY VIEW SERIAL/BIGSERIAL COLUMNS
order by sch.nspname, tab.relname, col.attnum;
postgresql

2 个回答

  1. 让我在 efesar 的回答中添加文档说明以下内容：

    数据类型 smallserial、serial 和 bigserial 不是真正的类型，而只是用于创建唯一标识符列的符号方便（类似于某些其他数据库支持的 AUTO_INCREMENT 属性）。在当前实现中，指定：

    CREATE TABLE tablename (
    colname SERIAL
);

    相当于指定：

    CREATE SEQUENCE tablename_colname_seq;
CREATE TABLE tablename (
    colname integer NOT NULL DEFAULT nextval('tablename_colname_seq')
);
ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname;

    这意味着如果

    • 列的数据类型是整数 (bigint) 并且
    • 它不是 NULL 并且
    • 它的默认值来自相关列所拥有的序列

    那么它是一个serial列。因此，按照您的建议（添加 ）检查目录中的这些因素NOT NULL就足以识别一serial列。

    有关查找（大）连续剧的实际查询，请参阅 Erwin Brandstetter 的出色回答。

    • 10
  2. Best Answer

    SERIAL和BIGSERIAL是一种伪类型。正如您所注意到的，它们实际上只是内部的INT和BIGINT。

    幕后发生的事情是 PostgreSQL 创建了一个序列并建立了对表的依赖关系。您可以在pg_class中搜索序列名称以及它与表的关系。

    pg_class： http ://www.postgresql.org/docs/9.2/static/catalog-pg-class.html

    SQL 小提琴： http ://sqlfiddle.com/#!12/dfcbd/6

    序列函数：http ://www.postgresql.org/docs/9.2/static/functions-sequence.html

    这篇 StackOverflow 帖子可能会有所帮助：https ://stackoverflow.com/questions/1493262/list-all-sequences-in-a-postgres-db-8-1-with-sql

    更新：您还可以使用pg_depend来确定哪些序列与您感兴趣的表/列相关：http ://www.postgresql.org/docs/9.2/static/catalog-pg-depend.html

    • 9

相关问题