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.


SELECT * FROM information_schema.columns WHERE table_schema='cruft' AND table_name='temp';

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;

2 Answers 2


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


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.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.