Posts tagged: PostgreSQL

PostgreSQL, how about you don’t make migration more complex

I am currently work on building a new site, from an existing one.  A lot of the front-end work is done, now to a lot of the admin.

Today a really quite annoying “feature” of PostgreSQL raises its head and takes up 3 hours of my time.  A lot of the data from the old site PHP/MySQL has been migrated to the new one Django/PostgreSQL which all worked fine, the front end works and its all lovely.  To maintain the old database id’s we inserted them all into the new database, this in itself is not the problem.  The problem arose what starting to adding new rows, which are expected to go to the end.

INSERT INTO table (title) VALUES ('my_title');

Instead of actually insert into a new row, this started working from id 1, throwing up errors whenever the id already existed and sometimes started to delete items from the end of the database.

Just what the hell.  After plenty of research and testing, initially expecting it to be a problem with my code and the problem is found.

Adding the Primary Key manually DOES NOT update the auto-increment value, and here is the fix (change field names where necessary)

SELECT SETVAL('sequence_name', (SELECT MAX(id) FROM table_name) + 1);

If you are using Django  you can generate the SQL by running, it does not run the automatically but you can pipe the response directly into the psql tool.

python sqlsequencereset appname

Very simple but this does not follow the MySQL style I am used to, then again SQLite also does things differently which had me stumped at first.