Postgresql INSERT - auto get next id?

user_596841 0
Viewed 8850

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:

enter image description here

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

intellij-idea postgresql
5 Answers

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.

user_5968410

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!

user_157099690

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

user_108224660
Related