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().

Gary - Stand with Ukraine · Accepted Answer · 2017-08-03 13:13:44Z

7

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

answered Aug 3, 2017 at 13:13

Gary - Stand with Ukraine

7,6291 gold badge33 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Vao Tsun · Accepted Answer · 2017-08-03 11:26:25Z

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 3, 2017 at 11:26

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

7 Comments

pookie Over a year ago

I've updated my post - I've tried both of those methods and unfortunately, neither work.

Łukasz Kamiński Over a year ago

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.

Vao Tsun Over a year ago

@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

pookie Over a year ago

@ŁukaszKamiński Yes, it seems there is a sequence. Please see updated question.

Vao Tsun Over a year ago

@ŁukaszKamiński thanks for your notice! I was answering OP's questions, loosing the actual problem

|

pookie · Accepted Answer · 2020-07-29 20:27:37Z

0

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.

answered Jul 29, 2020 at 20:27

pookie

4,16213 gold badges61 silver badges120 bronze badges

Comments

rj_murray · Accepted Answer · 2021-04-20 20:00:18Z

0

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!

answered Apr 20, 2021 at 20:00

rj_murray

11 bronze badge

Comments

Poorna Senani Gamage · Accepted Answer · 2019-12-30 08:47:00Z

-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, 2019 at 8:47

Poorna Senani Gamage

1,2682 gold badges20 silver badges32 bronze badges

answered Dec 30, 2019 at 7:05

siva shankar

11 bronze badge

Collectives™ on Stack Overflow

Postgresql INSERT - auto get next id?

5 Answers 5

Comments

7 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

7 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related