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

Question

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.

Craig Ringer · Accepted Answer · 2015-09-23 02:33:56Z

1

Use setval.

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

answered Sep 23, 2015 at 2:33

Craig Ringer

329k84 gold badges742 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related