Início / dba / Perguntas / 47098
Accepted
Joishi Bodio
Asked: 2013-07-27 11:39:03 +0800 CST

Como determino se uma coluna é definida como um tipo de dados serial em vez de um número inteiro baseado no catálogo?

  • 772

Atualmente, estou criando algum SQL para ler os catálogos do postgres (9.1) para criar definições de tabela. No entanto, estou encontrando um problema com os tipos de dados SERIAL/BIGSERIAL.

Exemplo:

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"

Ele me dá o nome do banco de dados (db), nome do esquema (cruft), nome da tabela (temp), nome da coluna (id), valor padrão (nextval( ... )) e tipo de dados (bigint e int8 .. NOT bigserial) ... Percebo que poderia apenas verificar se o valor padrão era uma sequência - mas não acredito que seria 100% preciso, pois poderia criar manualmente uma sequência e criar uma coluna não serial onde o valor padrão era aquela sequência.

Alguém tem uma sugestão de como posso fazer isso? Algo além de verificar o valor padrão de um nextval(*_seq)?

Editado para solução SQL adicionado aqui no caso de TL;DR ou novos usuários não familiarizados com o pg_catalog:

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 respostas

  1. Deixe-me acrescentar à resposta de efesar que a documentação afirma o seguinte:

    Os tipos de dados smallserial, serial e bigserial não são tipos verdadeiros, mas meramente uma notação conveniente para criar colunas de identificador exclusivo (semelhante à propriedade AUTO_INCREMENT suportada por alguns outros bancos de dados). Na implementação atual, especificando:

    CREATE TABLE tablename (
    colname SERIAL
);

    é equivalente a especificar:

    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;

    O que significa que se

    • o tipo de dados de uma coluna é inteiro (bigint) e
    • NÃO é NULO e
    • seu valor padrão vem de uma sequência pertencente à coluna em questão

    então é uma serialcoluna. Portanto, verificar esses fatores nos catálogos, como você propôs (com a adição de NOT NULL), são suficientes para identificar uma serialcoluna.

    Para uma consulta real para encontrar os (grandes) seriados, consulte a excelente resposta de Erwin Brandstetter.

    • 10
  2. Best Answer

    SERIAL e BIGSERIAL são tipos de pseudo-tipos. Como você notou, eles são apenas INT e BIGINT internamente.

    O que acontece nos bastidores é que o PostgreSQL cria uma sequência e configura uma dependência dela para a tabela. Você pode pesquisar pg_class pelo nome da sequência e como ela se relaciona com a tabela.

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

    SQL Fiddle: http://sqlfiddle.com/#!12/dfcbd/6

    Funções de sequência: http://www.postgresql.org/docs/9.2/static/functions-sequence.html

    Esta postagem do StackOverflow pode ser útil: https://stackoverflow.com/questions/1493262/list-all-sequences-in-a-postgres-db-8-1-with-sql

    ATUALIZAÇÃO : Você também pode usar pg_depend para descobrir quais sequências se relacionam com a tabela/coluna na qual você está interessado: http://www.postgresql.org/docs/9.2/static/catalog-pg-depend.html

    • 9

relate perguntas