CODEWITHSUNDEEP
mysqlpostgresqlmigrationsequencedatabase-administration
Chloe
Chloe

Reputation: 26294

MySQL => Postgres migration: How do I script the sequence start values?

I'm migrating from MySQL to Postgres with mysqldump and psql. I copied all the data but the serials start at 1. I need to update the starting value to the last id number. How can I script this to do it automatically? I tried

alter sequence keyword_id_seq restart (select max(id) from keyword);

It gave an error

ERROR:  syntax error at or near "(" at character 39
STATEMENT:  alter sequence keyword_id_seq restart (select max(id) from keyword);
ERROR:  syntax error at or near "("
LINE 1: alter sequence keyword_id_seq restart (select max(id) from k...

I can't set the start value when creating the sequence (with CREATE SEQUENCE table_colname_seq START 123;) because new values may be inserted into the old DB while I'm editing the file or before the data is copied.

Upvotes: 1

Views: 253

Answers (1)

Craig Ringer
Craig Ringer

Reputation: 324761

Use setval.

SELECT setval('keyword_id_seq', (select max(id) from keyword));

Upvotes: 1

Related Questions