Jak ustalić, czy kolumna jest zdefiniowana jako szeregowy typ danych zamiast liczby całkowitej na podstawie katalogu?

Obecnie tworzę trochę kodu SQL, aby czytać katalogi Postgres (9.1) w celu budowania definicji tabel. Mam jednak problem z typami danych SERIAL / BIGSERIAL.

Przykład:

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"

Daje mi nazwę bazy danych (db), nazwę schematu (cruft), nazwę tabeli (temp), nazwę kolumny (id), wartość domyślną (nextval (...)) i typ danych (bigint i int8 .. NIE bigserial) ... Zdaję sobie sprawę, że mogłem po prostu sprawdzić, czy wartością domyślną była sekwencja - ale nie sądzę, aby byłaby w 100% dokładna, ponieważ mogłem ręcznie utworzyć sekwencję i utworzyć kolumnę nieseryjną, w której wartością domyślną była ta sekwencja.

Czy ktoś ma jakieś sugestie, jak mogę to osiągnąć? Czy jest coś innego niż sprawdzenie wartości domyślnej dla nextval (* _ seq)?

Edytowane dla dodanego tutaj rozwiązania SQL w przypadku TL; DR lub nowych użytkowników nieznających 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;

SERIAL i BIGSERIAL są rodzajem pseudo-typów. Jak zauważyłeś, w rzeczywistości są one po prostu INT i BIGINT wewnętrznie.

Za kulisami PostgreSQL tworzy sekwencję i ustawia zależność od niej od tabeli. Możesz wyszukać pg_class w poszukiwaniu nazwy sekwencji i jej związku z tabelą.

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

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

Funkcje sekwencji: http://www.postgresql.org/docs/9.2/static/functions-sequence.html

Ten post StackOverflow może być pomocny: /programming/1493262/list-all-sequences-in-a-postgres-db-8-1-with-sql

AKTUALIZACJA : Możesz również użyć pg_depend, aby dowiedzieć się, które sekwencje odnoszą się do tabeli / kolumny, którą jesteś zainteresowany: http://www.postgresql.org/docs/9.2/static/catalog-pg-depend.html

Dodaję do odpowiedzi efesara, że ​​dokumentacja zawiera następujące informacje:

Typy danych smallserial, serial i bigserial nie są prawdziwymi typami, ale jedynie notacyjną wygodą do tworzenia unikalnych kolumn identyfikacyjnych (podobnie do właściwości AUTO_INCREMENT obsługiwanej przez niektóre inne bazy danych). W bieżącej implementacji, określając:

CREATE TABLE tablename (
    colname SERIAL
);

jest równoważne z określeniem:

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;

Co oznacza, że ​​jeśli

• typ danych kolumny to liczba całkowita (bigint) i
• to nie jest NULL i
• jego wartość domyślna pochodzi z sekwencji należącej do danej kolumny

to jest serialkolumna. Tak więc sprawdzenie tych czynników w katalogach, tak jak zaproponowałeś (z dodaniem NOT NULL), wystarcza do zidentyfikowania serialkolumny.

Rzeczywiste zapytanie dotyczące (dużych) seriali znajduje się w doskonałej odpowiedzi Erwina Brandstettera.