Thursday, September 12, 2013

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.