Craig

I am not asking you to re-write my sql so the bug will not show. I am presenting you with sql that fails and shows the bug. If every time someone reported a bug you ask them to re-write their sql so the bug is not hit, that would not eliminate the bug.

Also, you are using different timestamp string literals in your subquery. I am using the same constant datetime string literal in my example that the query processor does not need to cast to timestamp or anything to do the distinct part and eliminate duplicates.
insert into test_insert
(col1, col2)
select distinct
'b',
cast('2010-04-30 00:00:00' as timestamp)
>This works as expected. However is not an option because it is not generic sql. In PG timestamp data type is called 'timestamp' but in another DBMS it may be called  'datetime', etc.

... which is why your example is unsafe, and even if it appears to work
on other databases it is buggy. Instead, write:
My example is safe and is cross-dbms. I am not doing anything extra-ordinary just select distinct where a constant string expression is used in the select list.
select distinct
'b',
'2010-04-30 00:00:00'
Why is the sql above unsafe? It is not. It is simple select statement with two constant string expressions and distinct keyword. Now use the result of the sql above as source for inserting into test_table (col1, col2):
insert into test_insert
(col1, col2)
select distinct
'b',
'2010-04-30 00:00:00'
There is nothing unsafe here. You have a resultset that has one row with the values 'b',
'2010-04-30 00:00:00' being used to insert int col1, col2. Why would you say that's unsafe? '2010-04-30 00:00:00' is an ISO string literal being inserted into col2 whose data type is timestamp, perfectly safe.

Farid

On 6/5/2010 3:26 AM, Craig Ringer wrote: 
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.

--
Signature www.zidsoft.com CompareData:  compare and synchronize SQL DBMS data visually between two databases using ODBC drivers