Search code examples
postgresqlstored-procedurespsqlnode-postgres

Insert into table after pg dump

I used pg_dump to populate a table in new database. After that, I want to be able to insert rows into table using the default autoincrementer of the serial key. Here is what I have in table:

In this table (smtable), a is the key (set as serial).

--data filled using pg dump
a  |  b
1     2
2     5

Now when I do the following statement:

INSERT INTO smtable VALUES(DEFAULT, 6)
        RETURNING a INTO id;

I get the following error:

[error: duplicate key value violates unique constraint "a_pkey"]
detail: 'Key (a)=(1) already exists.

How do I get this statement to insert next_key of the table..

Solution

  • You might set the serial to the greatest number of your current id column to solve the issue. Since serial columns are associated with a sequence, just set it using this:

    SELECT setval('smtable_a_seq', max(a)) FROM table;