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@postgresql.org> wrote:

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

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

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@gmail.com>
wrote:

Akash Kava <ackava@gmail.com> writes:

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.

The reason addition of LIMIT causes problems is that it interposes
(in effect) an additional level of sub-select. When you write

insert into mytab (boolean_col) values ($1)

the parser is able to infer from the INSERT context that the type
of the unlabeled parameter symbol must be boolean. However, that
inference rule only extends to simple VALUES entries. If the
command gets any more complex, the parser will probably end up
falling back to a default assumption that unlabeled $1 is of type
text, and then later you get the can't-coerce failure. An unlabeled
literal string behaves about the same as a parameter symbol for this
purpose.

The fix, as already mentioned upthread, is to explicitly label
the parameter as being boolean. You could do this with a cast
in the query text:

insert into mytab (boolean_col) values ($1::boolean)

or your client-side library might have a way that the parameter
can be marked as being of the intended type when you submit the
query.

regards, tom lane