From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: ackava@gmail.com
Subject: BUG #18138: Using limit on VALUES causes type conversion to fail.
Date: Thu, 28 Sep 2023 12:44:51 +0000
Message-ID: <18138-aa7147890c7f16a3@postgresql.org> (raw)
The following bug has been logged on the website:
Bug reference: 18138
Logged by: Akash Kava
Email address: ackava@gmail.com
PostgreSQL version: 15.4
Operating system: alpine
Description:
Doing UPSERT is not easy and I know it is a complicated process. However,
since UPDATE has a WHERE clause, there is no way we can do conditional
INSERT.
But I was able to find a workaround, using LIMIT keyword with VALUES. I am
aware of the ON CONFLICT clause, but the issue with ON CONFLICT is, it
increases identity every time we want to update a row.
INSERT INTO the_table(column_1, column_2)
VALUES ($1, $2)
LIMIT LEAST((SELECT 1 FROM the_table WHERE key_1 = $3),2)-1
Basically this is a part of a larger query I have explained here.
https://stackoverflow.com/a/77190090/85597
This works as expected except for boolean and date fields.
Here is the example,
https://www.db-fiddle.com/f/g4LMVToHjrbYTDXT4MB1K/2
insert into avatar(username,url,is_public)
values ('a','b', 'true');
insert into avatar(username,url)
values ('a1','b')
limit LEAST((SELECT 1 FROM avatar),2)-1;
insert into avatar(username,url,is_public)
values ('a2','b', 'true')
limit LEAST((SELECT 1 FROM avatar),2)-1;
The problem occurs when we are sending data from node-postgres library.
Is there any work around? IF this will work correctly, we will be able to
use UPSERT easily without having gaps in identity sequences.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, ackava@gmail.com
Subject: Re: BUG #18138: Using limit on VALUES causes type conversion to fail.
In-Reply-To: <18138-aa7147890c7f16a3@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox