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
