PostgreSQL 選擇主鍵為“serial”或“bigserial”

在基於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
);

您的查詢將失敗，因為整數的標準名稱是“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：

• 在 Postgres 中安全、乾淨地重命名使用串列主鍵列的表？

您沒有為所提供的表格找到任何內容，因為您的查詢基於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<>在這裡擺弄

引用自：https://dba.stackexchange.com/questions/90555