[SQL] get sequence name from table name

[Uwe Maiwald]

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

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] Getting more than one row in UNIQUE fields

[Another Trad]

My table 'client' has the unique 'client_id'.
My test server, when I try:
select * from client where client_id = 12
My server returns 3 rows
When I try to update this client, his name for example,using my framework
(SQLAlchemy), obviously returns the error:
"Updated rowcount 3 does not match number of objects updated 1"
But the field client_id is unique and has a sequence to auto increment.

Anyone ever pass to this?

Re: [SQL] Getting more than one row in UNIQUE fields

[Tom Lane]

Another Trad  writes:
> My table 'client' has the unique 'client_id'.
> My test server, when I try:
> select * from client where client_id = 12
> My server returns 3 rows

We've seen bugs of that sort in the past ... what PG version is this,
and on what platform?

regards, tom lane

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] get sequence name from table name

[Gerardo Herzig]

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

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] get sequence name from table name

[Alvaro Herrera]

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 Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql