CODEWITHSUNDEEP
sqlpostgresqldatabase-trigger
alextc
alextc

Reputation: 3515

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

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.

Upvotes: 0

Views: 318

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247625

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;

Upvotes: 1

Related Questions