Greg,

Obviously I do not agree. When 14 different databases by 14 different DBMS vendors from the largest to the smallest in the market can do a simple thing as a using a subquery that has distinct keyword and your DBMS can't, I would say your DBMS is at fault and is not better, rather is lacking in this respect. I am not expecting favors from the DBMS by its doing what I expect it to do.

I do not want to beat an already dead horse, but if you review my example, you will see that it is very simple, PG already does conversion correctly from ISO string to timestamp column for inserting so you can't say we removed all conversions and that is a good thing, it is not. Basic feature of DBMS is allowing data entry into different data type columns using plain string literals. PG already does that and all other DBMS do that as well. For reference, although ODBC is not a DBMS, ODBC specification requires that an ODBC driver can convert all source DBMS data types from/to chars. This is not by accident, it is a necessity and is by design. I can understand that having multiple data formats for conversion to native data types from text can cause bugs and that's why we have established standards such as ISO for datetime/timestamp string formats and PG supports the conversion already.

The issue is the PG is not doing it correctly when 'distinct' keyword is used in the select statement. There is nothing buggy with using ISO datetime string literals to insert into a table timestamp column. There is no behind the scene magic going on.

1 Execute subquery: string literals are just that can be 'aa', 'bb', '2010-04-30 00:00:00', whatever, it does not matter what the string literal is.

2 Eliminate duplicates

3 Now a string literal is being inserted into a timestamp column, you have a string literal and you are asked to insert into a timestamp colum -> convert string literal to timestamp and do the insert

As you can see there is nothing buggy or heinous here, just simple select with distinct keyword in step 1, 2 and conversion from string literal to timestamp value in step 3

There is no ambiguity or magic to happen. Obviously in PG case there is some design or fault somewhere in this use-case when distinct keyword is used and is processed in step 2, that's all.

Farid

On 6/4/2010 10:41 PM, Greg Stark wrote: 
On Fri, Jun 4, 2010 at 11:15 PM, Farid Zidan <farid@zidsoft.com> wrote:
  
Now this not rocket science, it's simple insert statement where we do not
want duplicates inserted. Works on 10 other DBMSs.

    
I find usually when one person is arguing something is complex and
someone else is arguing it's simple it's the person who's claiming
it's simple who is wrong.

The other databases are not, I believe, preventing duplicates from
being inserted as you describe. They are removing duplicates from the
string constants and then silently converting to a different datatype
before inserting. When postgres removed these default casts to text it
turned up many instances where users had buggy code and Postgres had
been hiding from them by silently using string operators which was not
what users were expecting. In other words, while it might not matter
in this case, in general if you code in this style your code is buggy
and these other database implementations are not doing you any favours
by making it appear to work correctly most of the time.

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