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).
Arguably this could be improved, but so far nobody has figured out
anything better. This is working as intended.
-Kevin