How to specify the primary key seed for a postgres table?

Question

I have a postgres table that contains data where the primary key has been set by the code that injected the data. So lets say the primary keys are 0..49.

How can I instruct postgres to start with 50 on the primary key column for the next insert? Currently all inserts without a set primary key cause duplicate ID issues since its starting from 0 (I assume).

Answer 1

All you need to do is create a sequence with start 50 and set the default value for the primary key column as the next value for the sequence

Here's the code

CREATE SEQUENCE seq_name START 50;
ALTER TABLE table_name ALTER COLUMN column_name SET DEFAULT nextval(seq_name);

Answer 2

PostgreSQL's "Primary Key" essentially places a Unique constraint on the column (which implicitly places an Index on that/those columns as well). A unique constraint is (as far as I know,) no different than a primary key, but by name only; and you may only have one primary key per table. It's more for organizational purposes.

A Sequence is what actually iterates the Primary Key's value with each insert (if my assumptions about your set up are correct).

Therefore, what you're looking to do it modify the sequence, setting its START value to the last value inserted.

Example: Updating an existing sequence

SELECT SETVAL('name_of_my_sequence', 49);

• The first value passed to SETVAL is the name of the sequence to update.
• The second argument is the number we want to set the last value of the sequence to. The next insert into the table will use a value higher than the value set here.

Example: Creating a new sequence

CREATE SEQUENCE name_of_my_sequence
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 49 -- Tell the sequence to start off at 49, so the next insert will be 50.
  CACHE 1

If you don't know the name of your sequence, I'd highly recommend installing PgAdmin if you aren't doing so already. In there, expand the table in question, and expand "Columns". Click on the column which has the sequence, and in the top right pane look at "Default". You should see a value similar to nextval('name_of_my_sequence'::regclass). Whatever is in place of name_of_my_sequence if your sequence's name.

If you don't have a sequence set up yet, create your sequence using the example above. Then, all you need to do is right-click on your column, and click Properties. Under the Definition tab, enter something similar to nextval('name_of_my_sequence'::regclass) and you should be good to go! If you encounter any problems or have any other questions, let me know!