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
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 |