On 05/06/10 06:15, Farid Zidan wrote: > insert into test_insert > (col1, col2) > select *distinct* > 'b', > '2010-04-30 00:00:00' > >>Does not work. That's a bug.
Not really. select distinct * from (VALUES ('b','2010-04-30 00:00:00'), ('b','2010-04-30 00:00:00'), ('b','20100430 000000') ) AS x(a,b); Does that produce the result you expected? It certainly didn't deduplicate the timestamps, yet it's doing exactly the correct thing. So this won't work: create table test_insert ( col1 char(8) not null, col2 TIMESTAMP not null default CURRENT_TIMESTAMP, UNIQUE(col2) ); insert into test_insert (col1, col2) select a, b::timestamp from ( select distinct * from (VALUES ('b','2010-04-30 00:00:00'), ('b','2010-04-30 00:00:00'), ('b','20100430 000000') ) AS x(a,b)) AS y; ... which is why your example is unsafe, and even if it appears to work on other databases it is buggy. Instead, write: insert into test_insert (col1, col2) select distinct 'b', CAST('2010-04-30 00:00:00' AS timestamp); ... which will be safe on any database, is (AFAIK) perfectly standard, and is fuss free. -- Craig Ringer Tech-related writing: http://soapyfrogs.blogspot.com/ -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs