2

I had to import a large CSV file into a database, and one column must be a unique ID for a purchase. I set the type of the column to SERIAL (yes, I know it's not actually a type) but since I already had some data in there with their own "random" purchase IDs I'm not sure about what will happen when I insert new rows.

Will the purchase ID take the values that are not already in use? Will it start after the biggest existing ID? Will it start at 1 and not care about if a value is already in use?

Erwin Brandstetter
  • 479,275
  • 111
  • 893
  • 1,042
dieortin
  • 458
  • 5
  • 15

1 Answers1

2

The underlying SEQUENCE will not care about the values you inserted (explicitly providing values for the serial column, overruling the default), you have to adapt manually to avoid duplicate key errors:

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

'tbl' and 'id' being the names of table and column respectively.
Related:

Erwin Brandstetter
  • 479,275
  • 111
  • 893
  • 1,042