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: 20041028053330.GA43887@winnie.fuhr.org (view raw or flat)
Thread:
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');
 pg_get_serial_sequence 
------------------------
 public.foo_id_seq

Here's a query that you might find useful:

SELECT s1.nspname || '.' || t1.relname AS tablename,
       a.attname,
       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:

http://archives.postgresql.org/pgsql-general/2004-10/msg00673.php

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/

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-2014 The PostgreSQL Global Development Group