主页 / dba / 问题 / 90555
Accepted
John
Asked: 2015-01-30 06:30:02 +0800 CST

PostgreSQL 选择主键为“serial”或“bigserial”

  • 772

在基于PostgreSQL wiki页面创建新表时，我已经组合了一种方法来确定您在语法data_type中使用的内容。data_type

如果我的查询有问题，我实际上需要知道在给定场景中什么会在一个或多个查询在纯测试数据库/表上运行以修改该数据库/表的显式上下文中抛出它，所以运行这个查询以测试任何误报。

SELECT pg_attribute.attname, 
format_type(pg_attribute.atttypid, pg_attribute.atttypmod),
CASE 
WHEN format_type(pg_attribute.atttypid, pg_attribute.atttypmod)='bigint' THEN 'bigserial'
WHEN format_type(pg_attribute.atttypid, pg_attribute.atttypmod)='integer' THEN 'serial'
END AS type
FROM pg_index, pg_class, pg_attribute 
WHERE pg_class.oid = 'delete2'::regclass 
AND indrelid = pg_class.oid 
AND pg_attribute.attrelid = pg_class.oid 
AND pg_attribute.attnum = any(pg_index.indkey) 
AND indisprimary;

这是一个带有主键的表，该表不使用此查询返回主键：

CREATE TABLE delete_key_bigserial (
  test1 integer,
  id bigserial NOT NULL,
  col1 text,
  col2 text,
  test2 integer
);
postgresql primary-key

1 个回答

  1. Best Answer

    您的查询将失败，因为整数的标准名称是“integer”，而不是“int”。regtype您可以通过比较内部OID 而不是文本表示来避免此类错误。许多基本数据类型有多个别名，它们都解析为相同的内部注册类型。
    除此之外，您可以在很大程度上简化和改进：

    SELECT a.attname
     , CASE a.atttypid
         WHEN 'bigint'::regtype THEN 'bigserial'
         WHEN 'int'::regtype    THEN 'serial'
         ELSE format_type(a.atttypid, a.atttypmod)
       END AS type
FROM   pg_index     i
JOIN   pg_attribute a ON a.attrelid = i.indrelid
WHERE  i.indrelid = 'tbl'::regclass 
AND    i.indisprimary
AND    a.attnum = ANY(i.indkey);

    虽然这改进了查询，但它仍然没有按照您希望的那样做。

    仅仅因为整数列是（部分）主键，这还不能使它成为serial列。以下是对 a 的详细评估serial：

    您没有为所提供的表格找到任何内容，因为您的查询基于pg_index，这与序列类型完全无关。序列不必被索引，只有主键恰好被索引。

    安全解决方案

    只需检测以下的串行类型PRIMARY KEY：

    SELECT a.attrelid::regclass::text, a.attname
     , CASE a.atttypid
         WHEN 'int'::regtype  THEN 'serial'
         WHEN 'int8'::regtype THEN 'bigserial'
         WHEN 'int2'::regtype THEN 'smallserial'
       END AS serial_type
FROM   pg_attribute  a
JOIN   pg_constraint c ON c.conrelid  = a.attrelid
                      AND c.conkey[1] = a.attnum 
JOIN   pg_attrdef   ad ON ad.adrelid  = a.attrelid
                      AND ad.adnum    = a.attnum
WHERE  a.attrelid = 'tbl'::regclass   -- table name, optionally schema-qualified
AND    a.attnum > 0
AND    NOT a.attisdropped
AND    a.atttypid = ANY('{int,int8,int2}'::regtype[]) -- integer type
AND    c.contype = 'p'                 -- PK
AND    array_length(c.conkey, 1) = 1   -- single column
AND    pg_get_expr(ad.adbin, ad.adrelid)
     = 'nextval('''
    || (pg_get_serial_sequence (a.attrelid::regclass::text, a.attname))::regclass
    || '''::regclass)';                -- col default = nextval from owned seq

    如果 PK 不是串行类型，则不返回任何内容。

    @jpmc26 发表 评论

    一个简单的检查：

    pg_get_serial_sequence(attr.attrelid::regclass::text, attr.attname) IS NOT NULL

    只会检查列是否“拥有”序列，而不检查列默认值是否也设置为从序列中提取数字。文档：

    该函数可能应该被调用pg_get_owned_sequence；它的当前名称反映了它通常与 serialorbigserial列一起使用的事实。

    要显示具有正确数据类型的所有列 - 在适用的情况下替换为适当的serial类型：

    SELECT a.attrelid::regclass::text, a.attname
     , CASE WHEN a.atttypid = ANY ('{int,int8,int2}'::regtype[])
          AND EXISTS (
             SELECT FROM pg_attrdef ad
             WHERE  ad.adrelid = a.attrelid
             AND    ad.adnum   = a.attnum
             AND    pg_get_expr(ad.adbin, ad.adrelid)
                  = 'nextval('''
                 || (pg_get_serial_sequence (a.attrelid::regclass::text
                                          , a.attname))::regclass
                 || '''::regclass)'
             )
        THEN CASE a.atttypid
                WHEN 'int'::regtype  THEN 'serial'
                WHEN 'int8'::regtype THEN 'bigserial'
                WHEN 'int2'::regtype THEN 'smallserial'
             END
        ELSE format_type(a.atttypid, a.atttypmod)
        END AS data_type
FROM   pg_attribute  a
WHERE  a.attrelid = 'tbl'::regclass  -- table name, optionally schema-qualified
AND    a.attnum > 0
AND    NOT a.attisdropped
ORDER  BY a.attnum;

    对列默认值的检查可能会因search_path. 没有测试所有组合。

    db<>在这里摆弄

    • 9

相关问题