Error inserting into table with sequence in PostgreSQL

Recently had an issue when trying to insert a value into a table with a sequence. Our test system was returning errors from an application up attempted inserts. The sequence on a column in the table was WAY behind the actual maximum value in the table.


  1. select * FROM table_sequence; # Shows the last_value for the sequence
  2. select max(colname) FROM table1; # colname is the column in table1 that uses the sequence. Turns out the max was about 20k greater than the last_value from #1
  3. select setval('sequence_name',"max from #2 +1"); 
From #1 you will see the sequence_name along with last_value. Use those two in step 3 to get the sequence updated so that when it automatically generates the next value it will be one greater than what is already in the table.

Comments

Popular posts from this blog

Database, schema, and table sizes in Greenplum

Greenplum update with multiple tables

Show running queries on Postgresql/Greenplum