Message-ID: <4B194EBD.4050803@fmed.uba.ar>
Date: Fri, 04 Dec 2009 15:02:37 -0300
From: Gerardo Herzig <gherzig@fmed.uba.ar>
User-Agent: Thunderbird 2.0.0.6 (X11/20070801)
MIME-Version: 1.0
To: Uwe Maiwald <u.maiwald@kiss-net.de>
CC: pgsql-sql@postgresql.org
Subject: Re: get sequence name from table name
References: <4B193E8E.6010603@kiss-net.de>
In-Reply-To: <4B193E8E.6010603@kiss-net.de>
Content-Type: text/plain; charset=ISO-8859-15
Content-Transfer-Encoding: 7bit

Uwe Maiwald wrote:
> how to get the name of the sequence that is responsible for setting the
> autoincrement value of a tables primary key column?
> 
> i only have the name of the table and need to have an appropiate sql
> statement.
> 
> 
> i need this to write a program that loops through all tables of a
> database and then ajusts the start values of the sequencees in case the
> table has an automatic id value (serial/bigserial)
> 
> 
> thanks,
> Uwe
> 
The information schema provides what you need.

test=# create table testing (id serial);
NOTICE:  CREATE TABLE will create implicit sequence "testing_id_seq" for
serial column "testing.id"

test=# SELECT table_name, column_name, column_default from
information_schema.columns where table_name='testing';
 table_name | column_name |           column_default
------------+-------------+-------------------------------------
 testing    | id          | nextval('testing_id_seq'::regclass)
(1 row)

You may need an extra work finding out which the primary keys are, look
at the information schema docs [0]. Maybe you will also need the help of
the system catalogs [1].

[0] http://www.postgresql.org/docs/8.3/static/information-schema.html
[1] http://www.postgresql.org/docs/8.3/static/catalogs.html

(as you can see, this docs are from the 8.3 version. Check yours)

HTH
Gerardo