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
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
