Re: BUG #14853: Parameter type is required even when the query does not need to know the type

Hi

2017-10-16 7:40 GMT+02:00 Eduardo Pérez Ureta <edpeur(at)gmail(dot)com>:

> My example is even better!
> There is no need to infer the type as it is not needed!
> PostgreSQL should be able to infer that no type is needed.
>

PostgreSQL try to by type strict software. Sometimes the types can be
detected from context, sometimes not. Somewhere this missing information is
solved by type UNKNOWN, somewhere is raised a exception. Unfortunately
there is not 100% consistency - some API is very strict, some less, some
construct are very tolerant.

When you use a operator =, then unknown value should be casted to left side
type.

postgres=# select 1=1;
?column?
----------
t
(1 row)

postgres=# select 1='1';
?column?
----------
t
(1 row)

postgres=# select 1='a';
ERROR: invalid input syntax for integer: "a"
LINE 1: select 1='a';
^
Regards

Pavel

> On Oct 15, 2017 8:23 PM, "David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com>
> wrote:
>
>> On Sunday, October 15, 2017, Eduardo Pérez Ureta <edpeur(at)gmail(dot)com>
>> wrote:
>>
>>> I understand that not sending the type for a parameter (when it is not
>>> null) may not make much sense.
>>> But, currently PostgreSQL accepts parameters with unknown types in
>>> statements like:
>>> INSERT INTO t1 (col1) VALUES (?)
>>> SELECT * FROM t1 WHERE col1 = ?
>>> where the column can be different types like VARCHAR or TIMESTAMP
>>>
>>
>> col1 has a type and so the type of the unspecified variable can be
>> inferred. Your is null example cannot have its typed inferred.
>>
>> David J.
>>
>