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

Akash Kava <ackava(at)gmail(dot)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