Postgresql INSERT - auto get next id?

Question

I am using IntelliJ and the database tool. I had an issue with some data, so I deleted a row but then wanted to add a couple more...

I used the clone option:

I then modified my values and hit the commit button.

However, the insert failed with the error:

ERROR: duplicate key value violates unique constraint "Primary Key some_table" Detail: Key (id)=(58) already exists.

I then tried via the console and inserting manually, but I get the same error. Each time I do this, the id increments... I have thousands of records, so I cannot keep clicking until my finger snaps off.

This is what id looks like (when table is created):

id bigserial not null constraint "Primary Key some_table"
primary key

When I try to modify the table, I see id has a default value set as this:

nextval('some_table_id_seq'::regclass)

I've tried:

INSERT INTO some_table (id,...columns..) VALUES (DEFAULT,...columns...);

and

INSERT INTO some_table (...columns..) VALUES (...columns...);

but I get the same error...

I realise I could do something like running a query to get the MAX id and then do my insert, but that seems a little ridiculous to me.

How can tailor my INSERT so that it automatically gets the new/next id?

For example, MSSQL has automatically handles this for you using newid().

score 7 · Answer 1 · answered Aug 03 '17 at 13:13

If you sequence gets out of sync with the underlying data (due to manually entered records, or ids that came from the wrong sequence), you can reset them with a command such as:

SELECT SETVAL('some_table_id_seq',MAX(id)+1) FROM some_table

This just finds the highest ID in the table currently and then updates the sequence to the next available value.

Docs: https://www.postgresql.org/docs/current/static/functions-sequence.html

score 0 · Answer 2 · answered Aug 03 '17 at 11:26

0

You can either skip the column in column list on insert, or use DEFAULT keyword, like here:

t=# create table i (i bigserial, t text);
CREATE TABLE
Time: 99.579 ms
t=# insert into i (t) select null;
INSERT 0 1
Time: 75.390 ms
t=# insert into i values(DEFAULT,null);
INSERT 0 1
Time: 58.365 ms
t=# select * from i;
 i | t
---+---
 1 |
 2 |
(2 rows)

answered Aug 03 '17 at 11:26

Vao Tsun

47,234
13
100
132

I've updated my post - I've tried both of those methods and unfortunately, neither work. – pookie Aug 03 '17 at 11:38
2

It actually looks like he is in fact using sequence (considering he gets incremented value in each error message), but it lags behind of what is already inserted in database, because likely something inserted values with manually specified PK and that left sequence unused. – Łukasz Kamiński Aug 03 '17 at 11:40
@pookie If your sequence is behind the actual values, you can of course easily reset it - but first identify who is inserting values overheading the sequence – Vao Tsun Aug 03 '17 at 11:42
@ŁukaszKamiński Yes, it seems there is a sequence. Please see updated question. – pookie Aug 03 '17 at 11:43
@ŁukaszKamiński thanks for your notice! I was answering OP's questions, loosing the actual problem – Vao Tsun Aug 03 '17 at 11:44
@ŁukaszKamiński Just me using the DB. Records inserted via 1 virtual machine and I am trying to now insert a record manually on another virtual machine. I've not touched the id's - no idea what resetting the sequence means, what it entails or what the implications will be. – pookie Aug 03 '17 at 11:45
@pookie please read https://stackoverflow.com/questions/244243/how-to-reset-postgres-primary-key-sequence-when-it-falls-out-of-sync – Vao Tsun Aug 03 '17 at 11:46

score 0 · Accepted Answer · answered Jul 29 '20 at 20:27

Okay, I figured out what the problem was...

I had previously cloned the table, which then also cloned the sequence. This is what was causing the problem: the default value in the id column of some_table was using the sequence from the OLD, cloned table...

I modified the sequence name to use a new sequence, and it worked.

Also, when doing an insert, you can replace use:

INSERT INTO some_table (id,...columns..) VALUES (nextval('some_table_id_seq'::regclass),...columns...);

to automatically generate the id (same as referencing DEFAULT)

Thank you everyone for your help. Lesson learned: do not clone tables; don't be lazy.

score 0 · Answer 4 · answered Apr 20 '21 at 20:00

There is another way to reset it to what you want if you know what it should be and that is what follows: "alter sequence schema.table_id_seq restart with xx" (where xx is the next available number). This works with 12.x and 13.x and schema is the name of your schema and table is the name of your table!

score -2 · Answer 5 · edited Dec 30 '19 at 08:47

-2

You can try this,

 cur.execute("SELECT * FROM Employee_Activity")
    rows = cur.fetchall()
    for row in rows:
       val_id=(row[0])
    val_id+=1

val_id can be fetched and incremented automatically

edited Dec 30 '19 at 08:47

Poorna Senani Gamage

1,246
2
19
30

answered Dec 30 '19 at 07:05

siva shankar

1
1

Postgresql INSERT - auto get next id?

5 Answers5