ON CONFLICT on serial id

From: Mai Peng <maily(dot)peng(at)webedia-group(dot)com>
To: Pgsql-admin <pgsql-admin(at)lists(dot)postgresql(dot)org>
Cc: Bastien Pelletier-Garnier <bastien(dot)pelletier-garnier(at)webedia-group(dot)com>
Subject: ON CONFLICT on serial id
Date: 2021-03-09 17:51:15
Message-ID: 9A58FD18-21C7-43E9-98BA-72A3E587D3F3@webedia-group.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

Hello,
I have a function to perform an upsert. In order to use the id serial, if the operation is an insert, I use a coalesce.

CREATE OR REPLACE FUNCTION upsert_brand (
in_brand_id INTEGER,
in_brand_name TEXT
)
RETURNS VOID
AS $$
INSERT INTO brand (brand_id, brand_name)
VALUES(
coalesce(in_brand_id, (nextval('brand_id_seq'))),
in_brand_name
)
ON CONFLICT (brand_id)
DO
UPDATE
SET
brand_name = in_brand_name
WHERE
brand.brand_id = in_brand_id;
$$ LANGUAGE sql ;

1- Is this use case is recommended?
2- is there another way to prevent nextval ( sequence) from creating gap id if an error throw, or any trouble linked to unsafe transaction ?

Thanks in advance.
Mai

Browse pgsql-admin by date

  From Date Subject
Next Message postbox giridhar 2021-03-09 18:12:06 Lock after several failed login attempts
Previous Message Tom Lane 2021-03-08 15:58:12 Re: RESTRICT TABLE OWNER NOT TO DELETE IT