Ray O'Donnell schrieb am 22.04.2019 um 17:30:
I'm probably doing something silly.... I'm migrating data from one
database table to another, where the old table used a SERIAL primary
key and the new one uses GENERATED BY DEFAULT AS IDENTITY. Having
loaded the data into the new table, I need to reset the underlying
sequence so that it picks up from the highest existing value.
I'm using PostgreSQL 11.2 on Debian 9.
I've tried:
=# alter table orders alter column order_id restart with (
select max(order_id) + 1 from orders);
ERROR: syntax error at or near "("
LINE 1: ...r table orders alter column order_id restart with (select ma...
What am I missing?
I should add that this is part of a larger migration script; otherwise I could just do it by hand the command line.
As you noticed, an identity column is backed by a sequence, just like a serial column, so you can use setval() to sync the sequence.
To get the name of the sequence you can also use pg_get_serial_sequence() (despite its name):
select setval(pg_get_serial_sequence('orders', 'order_id'), (select max(order_id) from x));
Thomas
