PostgreSQL - How to define a trigger function to update the column with (the maximum value of the column + 1) of existing records

Question

In the PostgreSQL database, the item table is defined

CREATE TABLE item
(
  "objectId" text NOT NULL,
  "createdAt" timestamp with time zone,
  "updatedAt" timestamp with time zone,
  _rperm text[],
  _wperm text[],
  item_name text,
  item_id integer,
  CONSTRAINT "ITEM_pkey" PRIMARY KEY ("objectId")
)
WITH (
  OIDS=FALSE
);
ALTER TABLE item
  OWNER TO gc;

I would like to create a trigger on this table. When a new record is being inserted (not updated), the item_id column should have the maximum value of all existing item_id values plus 1. This is mimicing the auto-incrementing behaviour on item_id.

if two transactions insert rows at the same time, there may be duplicate item_id with this approach. is that acceptable? — Haleemur Ali
– Haleemur Ali, Commented Jul 26, 2018 at 4:55
Do not do that. A serial (or identity) using a sequence is the only sane, performant and scalable solution to create unique numbers. — user330315
– user330315, Commented Jul 26, 2018 at 6:17

Laurenz Albe · Accepted Answer · 2018-07-26 05:38:53Z

1

I recommend that you do not search the table for the maximal value, since that makes you vulnerable to race conditions.

Rather, you should create a second one-element table that holds the current maximum.

Then you can get the next value in a way that is safe from race conditions:

UPDATE maxtab SET id = id + 1 RETURNING id;

answered Jul 26, 2018 at 5:38

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

Collectives™ on Stack Overflow

PostgreSQL - How to define a trigger function to update the column with (the maximum value of the column + 1) of existing records

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related