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/
