Início / dba / Perguntas / 90555
Accepted
John
Asked: 2015-01-30 06:30:02 +0800 CST

PostgreSQL SELECT chave primária como "serial" ou "bigserial"

  • 772

Combinei uma maneira de determinar o que data_typeé data_typeusado na sintaxe ao criar uma nova tabela com base na página wiki do PostgreSQL .

Se houver algo errado com minha consulta, preciso realmente saber o que , em um determinado cenário, o prejudicaria no contexto explícito de ter uma consulta ou consultas a serem executadas em um banco de dados/tabela puramente de teste para modificar esse banco de dados/tabela, então execute isto query para testar quaisquer falsos positivos.

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;

Aqui está uma tabela com uma chave primária que não retorna a chave primária com esta consulta:

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

1 respostas

  1. Best Answer

    Sua consulta falharia porque o nome padrão de um número inteiro é "inteiro", não "int". Você pode evitar esse tipo de erro comparando o regtypeOID interno em vez de uma representação de texto. Muitos tipos de dados básicos têm vários nomes de alias, todos eles resolvem para o mesmo tipo registrado interno.
    Além disso, você pode simplificar e melhorar bastante:

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

    Embora isso melhore a consulta, ainda não está fazendo o que você espera .

    Só porque uma coluna inteira é (parte da) chave primária, isso ainda não a torna uma serialcoluna. Aqui está uma avaliação detalhada do que serialé:

    Você não encontra nada para a tabela apresentada, pois está baseando sua consulta em pg_index, que não tem nada a ver com tipos seriais. Uma série não precisa ser indexada, apenas as chaves primárias são indexadas.

    Soluções seguras

    Apenas detecte o tipo serial do 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

    Não retorna nada se o PK não for do tipo serial.

    Dirigindo-se ao comentário de @jpmc26

    Uma verificação simplificada para apenas:

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

    verificaria apenas se existe uma sequência "de propriedade" da coluna, mas não se o padrão da coluna também está definido para extrair números da sequência. A documentação:

    A função provavelmente deveria ter sido chamada pg_get_owned_sequence; seu nome atual reflete o fato de que normalmente é usado com colunas serialou .bigserial

    Para mostrar TODAS as colunas com o tipo de dados adequado - substituído pelo serialtipo apropriado quando aplicável:

    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;

    A verificação no padrão da coluna pode ser interrompida com configurações exóticas para search_path. Não testei todas as combinações.

    db<>mexa aqui

    • 9

relate perguntas