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

Question

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)

[Deleting a table in PostgreSQL without deleting an associated sequence](http://stackoverflow.com/questions/4328884/deleting-a-table-in-postgresql-without-deleting-an-associated-sequence) has good answers that are applicable to this. — PhilHibbs, Aug 01 '16 at 10:46

score 11 · Answer 1 · answered Nov 09 '12 at 13:56

11

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.

answered Nov 09 '12 at 13:56

Richard Huxton

21,516
3
39
51

THX, thats exactly what I asked for (though not what I needed :-D) – Maddog Nov 09 '12 at 14:38

score 2 · Accepted Answer · answered Nov 09 '12 at 13:57

2

try with:

python manage.py sqlsequencereset <appname>

and feed that to psql cli

HTH

answered Nov 09 '12 at 13:57

mpaolini

1,334
10
7

THX, thats exactly what I needed (though not what I asked for :-D) – Maddog Nov 09 '12 at 14:37
@Maddog wow, so I can understand needs that lie beyond questions! – mpaolini Nov 09 '12 at 14:40

score 0 · Answer 3 · answered May 09 '19 at 06:43

0

Use this to get the sequence name along with count.

SELECT sequence_name,start_value from information_schema.sequences;

answered May 09 '19 at 06:43

Pradeep Maurya

384
2
8

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

3 Answers3