So I'm currently creating some SQL to read through the postgres (9.1) catalogs to build table definitions. However, I am encountering a problem with SERIAL/BIGSERIAL data types.
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"
It gives me database name (db), schema name (cruft), table name (temp), column name (id), default value (nextval( ... )), and data type (bigint and int8 .. NOT bigserial) ... I realize that I could just check to see if the default value was a sequence - but I don't believe that would be 100% accurate since I could manually create a sequence and create a non serial column where the default value was that sequence.
Does anyone have a suggestion for how I might accomplish this? Anything other than checking the default value for a nextval(*_seq)?
Edited for SQL solution added here in case of TL;DR or new users unfamiliar with the 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;