How do I determine if a column is defined as a serial data type instead of an integer based off the catalog?

Question

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.

Example:

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;

Answer 1

SERIAL and BIGSERIAL are kind of pseudo-types. As you noticed, they are really just INT and BIGINT internally.

What happens behind the scenes is that PostgreSQL creates a sequence and sets up a dependency on it to the table. You can search pg_class for the sequence name and how it relates to the table.

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

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

Sequence Functions: http://www.postgresql.org/docs/9.2/static/functions-sequence.html

This StackOverflow post might be helpful: https://stackoverflow.com/questions/1493262/list-all-sequences-in-a-postgres-db-8-1-with-sql

UPDATE: You can also use pg_depend to figure out which sequences relate to the table /column you are interested in: http://www.postgresql.org/docs/9.2/static/catalog-pg-depend.html

Answer 2

Let me add to efesar's answer that the documentation states the following:

The data types smallserial, serial and bigserial are not true types, but merely a notational convenience for creating unique identifier columns (similar to the AUTO_INCREMENT property supported by some other databases). In the current implementation, specifying:

CREATE TABLE tablename (
    colname SERIAL
);

is equivalent to specifying:

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;

Which means that if

• a column's data type is integer (bigint) and
• it is NOT NULL and
• its default value comes from a sequence owned by the column in question

then it is a serial column. So checking these factors in the catalogs, as you proposed (with the addition of NOT NULL), are sufficient to identify a serial column.

For an actual query to find the (big)serials, see the excellent answer of Erwin Brandstetter.