Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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;
share|improve this question
1  
Later, related answer with code examples: dba.stackexchange.com/questions/90555/… –  Erwin Brandstetter Jan 30 at 21:27

2 Answers 2

up vote 6 down vote accepted

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: http://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

share|improve this answer
    
Thank you - I will look into this and vote for a correct answer on monday. :) –  Joishi Bodio Jul 26 '13 at 22:16

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.

share|improve this answer
    
Thank you - I will look into this and vote for a correct answer on monday. :) –  Joishi Bodio Jul 26 '13 at 22:17
    
a column's data type is integer (bigint) and - integer or bigint? –  Alex Apr 10 '14 at 14:01
    
after all, this colname integer NOT NULL DEFAULT nextval('tablename_colname_seq') says it is just integer. –  Alex Apr 10 '14 at 14:01
    
@Alex there is an other pseudotype called bigserial, I referred to that. –  dezso Apr 10 '14 at 14:15
    
so SERIAL - int, bigserial - bigint? –  Alex Apr 10 '14 at 14:30

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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