Обсуждение: BUG #18138: Using limit on VALUES causes type conversion to fail.

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.

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