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

<meta content="text/html; charset=ISO-8859-1"
 http-equiv="Content-Type">


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:
<blockquote cite="mid:4C09FC41.4000304@postnewspapers.com.au"
 type="cite">
  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 <font
 size="-1">between two databases
using ODBC drivers