Re: Re: BUG #5490: Using distinct for select list causes insert of timestamp string literal to fail

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/