Re: BUG #18138: Using limit on VALUES causes type conversion to fail.

On Thu, Sep 28, 2023 at 1:48 PM PG Bug reporting form <
noreply(at)postgresql(dot)org> wrote:

> The following bug has been logged on the website:
>
> Bug reference: 18138
> Logged by: Akash Kava
> Email address: ackava(at)gmail(dot)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
>

A smaller example would be that this works and inserts the row:

insert into avatar(username, url, is_public)
values ('a','b', 'true');

while you get the error with:

insert into avatar(username, url, is_public)
values ('a2','b', 'true')
limit 1 ;

I am not sure if this would be classified as a bug since you are putting
quotes around the boolean value.
Without quotes it would work fine:

insert into avatar(username, url, is_public)
values ('a1','b', true)
limit 1 ;

or if you explicitly converted to the type of the column:

insert into avatar(username, url, is_public)
values ('a2','b', 'true'::boolean)
limit 1 ;

Best regards

Pantelis Theodosiou