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

Hi,

Thanks for the reply, but if you try the values with parameters, using some
library like node-pg or postgres c# connector, it fails when we
parameterize the query.

insert into avatar(username, url, is_public)
values ($1,$2,$3)
limit 1 ;

Or the values are coming from some other table. The problem is with `limit`
not how you send the values.

Thank you,
- Akash Kava

On Thu, Sep 28, 2023 at 8:17 PM Pantelis Theodosiou <ypercube(at)gmail(dot)com>
wrote:

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