Re: BUG #5490: Using distinct for select list causes insert of timestamp string literal to fail

"Farid Zidan" <farid@zidsoft.com> wrote:

> insert into test_insert
> (col1, col2)
> select distinct
> 'b',
> '2010-04-30 00:00:00'
>
>
> ERROR:  column "col2" is of type timestamp without time zone but
> expression is of type text
> LINE 16: '2010-04-30 00:00:00'
>          ^
> HINT:  You will need to rewrite or cast the expression.

Try using a timestamp literal instead of a bare literal:

insert into test_insert
(col1, col2)
select distinct
'b',
timestamp '2010-04-30 00:00:00'

This is actually working as intended in all the cases you showed, so
it isn't a bug.  If we were strictly complying with the SQL
standard, your first example would also fail, but we are more
lenient than the standard where we can be, to allow an unadorned
literal to be an UNKNOWN type until something causes it to be
resolved, to allow people to omit the type decoration in many cases.
To determine that something is a distinct value, you have to
determine a type for it (otherwise you won't know if '2010-04-30
00:00:00' is the same as '2010-04-30 00:00:00.0', for example), so
if you don't tell it otherwise, it will assume text -- leading to
the behavior you saw.

-Kevin