CODEWITHSUNDEEP
sqlpostgresqlprimary-key
puk
puk

Reputation: 16762

manually updating primary key

We are dealing with legacy code that doesn't auto-increment the primary key (see serial) so I have to manually do it. What is the correct way to manually update the primary key field on insert. I am getting an error when I do the below

Table:

CREATE TABLE pizza (
    id bigint not null,
    price int
)

Insert statement:

INSERT INTO pizza
    (id, price)
VALUES
    (
    (SELECT max(id) from pizza)+1,
    1.75
    )

Upvotes: 0

Views: 75

Answers (1)

user330315
user330315

Reputation:

Don't use max()+1 to generate a primary key. It's not safe for concurrent inserts and it doesn't really scale well.

Just create a sequence and use that:

create sequence pizza_id_seq;

Then synchronize it with the current values in the table:

select setval('pizza_id_seq', coalesce(max(id),1))
from pizza;

Then, instead of changing your INSERT statements to use the dreaded max() + 1, just use the sequence:

INSERT INTO pizza
  (id, price)
VALUES
  (nextval('pizza_id_seq'), 1.75)

Upvotes: 3

Related Questions