Hello Greg,

I suspect what they're doing is doing a DISTINCT of the text values
and then converting the results. That's not the same as what you're
suggesting it do (insert distinct timestamp values) since different
text values can represent the same timestamp. For example look at what
That's a good point. I think you are correct. When the query parser is in the nested subselect it only sees string literals for the timestamp column values (does not know it is a timestamp yet). However, when it gets to do the insert it then must convert the string literals to timestamp values because at that point it knows that the string literal is to be inserted into a timestamp column.

Since I am using a constant string literal for the timestamp it really does not matter when the conversion takes place.

select distinct
<col1>,
<col2>,
..
'2010-04-30 00:00:00'
from
....

the timestamp string literal is a constant and really does not affect the distinct resultset in anyway. I do need to stamp all the inserts with a specific timestamp value and that's why I am using a constant string literal. If I used an expression such as current_timestamp/(ODBC {fn now()}, then that would factor into the distinct clause and pollute the distinctness of subquery reulsultset.

Here is actual statements I am running and like I said they work for all 9+ DBMSs (I use ODBC and{fn user()} is the ODBC cross-dbms syntax for the current user ID):
insert into
in_sync_node_toolbar
(node_no, sync_cd, toolbar_cd,
ctrl_ins_dtm, ctrl_upd_dtm, ctrl_usr_id)
select distinct
isnr.node_no,
case
  when isr.rs_type_cd = 'TABLELS' then 'CMPTS'
  when isr.rs_type_cd = 'PROCLS' then 'CMPPROCS'
  when isr.rs_type_cd = 'SEQLS' then 'CMPSEQS'
  else null
end,
'TBCSCPT1',
'2010-04-30 00:00:00',
'2010-04-30 00:00:00',
{fn user() }
from
in_sync_node_resultset isnr,
in_sync_object_pattern isop,
in_sync_resultset isr
where
(isnr.rs_oid = isr.rs_oid or
 isnr.rs_oid_other = isr.rs_oid) and
isr.rs_oid = isop.rs_oid and
isr.rs_type_cd in ('TABLELS', 'PROCLS', 'SEQLS');

insert into
in_sync_node_toolbar
(node_no, sync_cd, toolbar_cd,
ctrl_ins_dtm, ctrl_upd_dtm, ctrl_usr_id)
select distinct
isnr.node_no,
case
  when isr.rs_type_cd = 'TBLVIEW' then 'CMPTABLE'
  when isr.rs_type_cd = 'PROC' then 'CMPPROC'
  when isr.rs_type_cd = 'SEQ' then 'CMPSEQ'
  else null
end,
'TBCSCPT1',
'2010-04-30 00:00:00',
'2010-04-30 00:00:00',
{fn user() }
from
in_sync_node_resultset isnr,
in_sync_object iso,
in_sync_resultset isr
where
(isnr.rs_oid = isr.rs_oid or
 isnr.rs_oid_other = isr.rs_oid) and
isr.rs_oid = iso.rs_oid and
isr.rs_type_cd in ('TBLVIEW', 'PROC', 'SEQ');


This is the problem with depending on non-standard extensions. You're
never really sure that they're working. They be working on some
systems but doing something unexpected on other systems.
All the other DBMSs doing is a select distinct on the subquery that has the constant timestamp string literals. There is nothing non-standard or ambiguous there. As far as the DBMS is concerned the constant string expression is just a string literal and can represent anything.

Now the issue is that when the other DBMSs get to do the insert part they are able, as one would expect, to convert the subquery resultset string literal column to a timestamp column. I think PostreSQL is doing the first part (subquery with distinct clause correctly), but when it gets to use the resultset of the subquery in the insert it "forgets" how to convert
'2010-04-30 00:00:00' to timestamp value (but forgets only when 'distinct' is used in the subquery!)

Farid

On 6/4/2010 4:18 PM, Greg Stark wrote: 
On Fri, Jun 4, 2010 at 7:18 PM, Farid Zidan <farid@zidsoft.com> wrote:
  
If a simple SQL statement works on 9+ different databases
    
For what it's worth are you sure it works as you expect in these other
databases?

I suspect what they're doing is doing a DISTINCT of the text values
and then converting the results. That's not the same as what you're
suggesting it do (insert distinct timestamp values) since different
text values can represent the same timestamp. For example look at what
this does:

 select cast(x as timestamp with time zone) from (select distinct x
from (values ('2010-01-01 12:00PM UTC'), ('2010-01-01 7:00AM EST')) as
x(x)) as y;

If you inserted those values into a table with a timestamp with time
zone column you would get duplicate values even with the distinct.

This is the problem with depending on non-standard extensions. You're
never really sure that they're working. They be working on some
systems but doing something unexpected on other systems.

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