sqldatabasepostgresql

What happens when you insert rows in a SERIAL column with existing values?

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?

Solution

  • The underlying SEQUENCE does not know about values inserted manually (overriding the default). If any of them is equal or greater than the next sequence value, you have to set the sequence manually to avoid duplicate values - duplicate key errors if the column is defined unique:

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

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