CODEWITHSUNDEEP
databasedjangopostgresqlsequences
Maddog
Maddog

Reputation: 4816

How can I get all sequences bound with a table in PostgreSQL

I recently switched from MySQL to PostgreSQL in my Django app and I need to set last values of all sequences on max. number of corresponding table and column. How can I get all sequences related to a table? So far I am doing somethign like:

SELECT sequence_name FROM information_schema.sequences 
WHERE sequence_name LIKE 'table_name%';

but I dont't like this approach, mostly because I have to additionally clean the results. Is there some ID that connects table with their sequences.

Now I can't add any object to my database, because Postgre "auto increment" is running from 1 and new objects' PKs are clashing with existing records.

Note: My django app is quite large and I am using several additional package, so the naming convention is not consistent (table names are with / without app pefix, PK columns are like 'tableid' or 'table_id' of just 'id' and so on)

Upvotes: 2

Views: 13228

Answers (3)

Pradeep Maurya
Pradeep Maurya

Reputation: 414

Use this to get the sequence name along with count.

SELECT sequence_name,start_value from information_schema.sequences;

Upvotes: 0

mpaolini
mpaolini

Reputation: 1344

try with:

python manage.py sqlsequencereset <appname>

and feed that to psql cli

HTH

Upvotes: 2

Richard Huxton
Richard Huxton

Reputation: 22943

Assuming you're not doing anything too unusual, try the following two queries.

SELECT * FROM information_schema.sequences ;
SELECT * FROM information_schema.columns WHERE column_default LIKE 'nextval%';

That should tell you what you need to know. I'd probably just strip the sequence-name out of the column_default field.

Upvotes: 11

Related Questions