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