It’s pretty simple to update the next value generated by a sequence, but what if you want to update them for every table?
In our case, we had been using DMS to import data, but none of the sequences were updated afterwards. So any attempt to insert a new row was doomed to failure.
To update one sequence you can call:
SELECT setval('foo.bar_id_seq', (select max(id) from foo.bar), true);
and you can get a list of tables pretty easily:
\dt *.*
but how do you put them together? My first attempt was using some vim fu (qq@q), until I realised I’d need to use a regex to capture the table name. And then I found some sequences that weren’t using the same name as the table anyway (consistency uber alles).
It’s also easy to get a list of sequences:
SELECT * FROM information_schema.sequences;
but how can you link them back to the table?
The solution is a function called pg_get_serial_sequence:
select t.schemaname, t.tablename, pg_get_serial_sequence(t.schemaname || '.' || t.tablename, c.column_name)
from pg_tables t
join information_schema.columns c on c.table_schema = t.schemaname and c.table_name = t.tablename
where t.schemaname <> 'pg_catalog' and t.schemaname <> 'information_schema' and pg_get_serial_sequence(t.schemaname || '.' || t.tablename, c.column_name) is not null;
This returns the schema, table name, and sequence name for every (non-system) table; which should be “trivial” to convert to a script updating the sequences (I considered doing that in sql, but dynamic tables aren’t easy to do).
