pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedget sequence name from table name
3+ messages / 3 participants
[nested] [flat]
* get sequence name from table name
@ 2009-12-04 16:53 Uwe Maiwald <u.maiwald@kiss-net.de>
2009-12-04 18:02 ` Re: get sequence name from table name Gerardo Herzig <gherzig@fmed.uba.ar>
2009-12-04 19:24 ` Re: get sequence name from table name Alvaro Herrera <alvherre@commandprompt.com>
0 siblings, 2 replies; 3+ messages in thread
From: Uwe Maiwald @ 2009-12-04 16:53 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: get sequence name from table name
2009-12-04 16:53 get sequence name from table name Uwe Maiwald <u.maiwald@kiss-net.de>
@ 2009-12-04 18:02 ` Gerardo Herzig <gherzig@fmed.uba.ar>
1 sibling, 0 replies; 3+ messages in thread
From: Gerardo Herzig @ 2009-12-04 18:02 UTC (permalink / raw)
To: Uwe Maiwald <u.maiwald@kiss-net.de>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: get sequence name from table name
2009-12-04 16:53 get sequence name from table name Uwe Maiwald <u.maiwald@kiss-net.de>
@ 2009-12-04 19:24 ` Alvaro Herrera <alvherre@commandprompt.com>
1 sibling, 0 replies; 3+ messages in thread
From: Alvaro Herrera @ 2009-12-04 19:24 UTC (permalink / raw)
To: Uwe Maiwald <u.maiwald@kiss-net.de>; +Cc: pgsql-sql
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?
You can use the pg_get_serial_sequence() function. You need the name of
the column in addition to the table name though.
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2009-12-04 19:24 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-12-04 16:53 get sequence name from table name Uwe Maiwald <u.maiwald@kiss-net.de>
2009-12-04 18:02 ` Gerardo Herzig <gherzig@fmed.uba.ar>
2009-12-04 19:24 ` Alvaro Herrera <alvherre@commandprompt.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox