I have a table with a few rows in it. It has a primary. I insert a large number of rows, including all fields including the primary key. Now when I try to an insert without specifying the primary key it fails because it's trying to use a number that was already used as a primary key in the previously inserted rows. How can I tell the table to create new primary keys at numbers higher than the existing keys?

For example, say I have rows with primary keys 1-8 in the table and I do a mass insert of rows with primary keys 9 through 64. Then I try to do a single insert without a primary key and it fails because it's trying to use 9. The next try fails because it's trying to use 10. And so on. I want to tell the table to start counting new primary keys at 65. How can I do that?

1 Answer 1

SELECT setval(pg_get_serial_sequence('tbl', 'tbl_id'), max(tbl_id)) FROM tbl; 

Table name and column name are two separate parameters to the pg_get_serial_sequence() function.
And you don't need a subselect.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.