Re: datatype of constant is not propagated into aggregate query

Hello

2012/3/11 Kevin Grittner <Kevin.Grittner@wicourts.gov>:
> Pavel Stehule  wrote:
>
>> create table t1(d date, n integer);
>>
>> postgres=# insert into t1 select '2001-01-01', 1 from
>> generate_series(1,3);
>> INSERT 0 3
>>
>> but
>>
>> postgres=# insert into t1 select distinct '2001-01-01', 1 from
>> generate_series(1,3);
>> ERROR: column "d" is of type date but expression is of type text
>
> This has been discussed many times before.  If you use a date
> literal, you are fine.  For example, this does work:
>
> insert into t1 select distinct date '2001-01-01', 1
>  from generate_series(1,3);
>
>> HINT: You will need to rewrite or cast the expression.
>
> The hint is even on point.
>
> In PostgreSQL a quoted literal is taken as type "unknown" and it can
> often be coerced to the right type based on its usage.  The reason
> the first example works is that the literal of unknown type is being
> assigned to a date column in the insert.  In the second example it is
> being used for DISTINCT, and we don't look deeper to see what is
> later done with that later.  Type matters for DISTINCT, because
> (depending locale) you might want '2011-12-31' and '12/31/2011' to be
> taken as identical values.  In the absence of clues as to what type
> to use, PostgreSQL defaults to text, and you can't assign a text
> value to the date column (without a cast).

it doesn't work with enums where must not be any dependency on locale

postgres=# create type e as enum('A','B');
CREATE TYPE
postgres=# create table hh (_e e);
CREATE TABLE
postgres=# insert into hh select 'A';
INSERT 0 1
postgres=# insert into hh select distinct 'A';
ERROR:  column "_e" is of type e but expression is of type text
LINE 1: insert into hh select distinct 'A';                                      ^
HINT:  You will need to rewrite or cast the expression.

>
> Arguably this could be improved, but so far nobody has figured out
> anything better.  This is working as intended.

ook

Regards

Pavel
>
> -Kevin