Skip site navigation (1) Skip section navigation (2)

Re: determine sequence name for a serial

From: Michael Fuhr <mike(at)fuhr(dot)org>
To: Robby Russell <robby(at)planetargon(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: determine sequence name for a serial
Date: 2004-10-28 05:33:30
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-general
On Wed, Oct 27, 2004 at 09:06:15PM -0700, Robby Russell wrote:
> Ok, so how would I go about getting the sequence name for a SERIAL
> field on any given schema.table? I would like to build a function
> that would return this value if I pass it the schema and table (and
> fieldname is necessary)

PostgreSQL 8.0 (still in beta) has pg_get_serial_sequence():

test=> SELECT pg_get_serial_sequence('foo', 'id');

Here's a query that you might find useful:

SELECT s1.nspname || '.' || t1.relname AS tablename,
       s2.nspname || '.' || t2.relname AS sequencename
FROM pg_depend AS d
JOIN pg_class AS t1 ON t1.oid = d.refobjid
JOIN pg_class AS t2 ON t2.oid = d.objid
JOIN pg_namespace AS s1 ON s1.oid = t1.relnamespace
JOIN pg_namespace AS s2 ON s2.oid = t2.relnamespace
JOIN pg_attribute AS a ON a.attrelid = d.refobjid AND a.attnum = d.refobjsubid
WHERE t1.relkind = 'r'
  AND t2.relkind = 'S';

I posted a somewhat different query in a recent thread about
automatically updating all sequences after importing data:

Michael Fuhr

In response to

pgsql-general by date

Next:From: Jonathan DaughertyDate: 2004-10-28 05:45:48
Subject: Re: determine sequence name for a serial
Previous:From: Tom LaneDate: 2004-10-28 05:15:04
Subject: Re: determine sequence name for a serial

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group