Hello Kevin,

I can't help but wonder why you resist using the standard syntax.
I am using the standard syntax. Single quote in sql denotes a string. so '2010-04-30 00:00:00' is string literal. That's universal. Now you want me to use PG-specific timestamps and that's like I said is not standard/cross-dbms.

I have just finished testing with Ingre 9.2 and it works there too. That's 10 DBMSs systems that use single quotes to denote a string literal and can covert ISO-standard datetime string literal to timestamp.

You can't not interpret string literals one way in one statement and just because user uses the word 'distinct' decide to switch paradigms. That's not good design or planning. Of course you can decide to do whatever you want, just do not expect developers to start special-coding just for PostreSQL because you decide to cast correctly or not correctly depending on whim.

Let me reiterate the example, maybe it was too terse and you did not read it carefully,

create table test_insert (
col1 char(8) not null,
col2 TIMESTAMP not null default CURRENT_TIMESTAMP);
>create the test table. No issue.

insert into 
test_insert 
(col1, col2) values
('a', '2010-04-30 00:00:00');
>Works like expected, PG correctly converts standard ISO-datetime string literal to timestamp. No issue.

insert into test_insert
(col1, col2)
select
'b',
'2010-04-30 00:00:00'
>That works too. No issue.

insert into test_insert
(col1, col2)
select distinct
'b',
'2010-04-30 00:00:00'
>Does not work. That's a bug.

Now this not rocket science, it's simple insert statement where we do not want duplicates inserted. Works on 10 other DBMSs.

FAA stuff and other is not related to this bug. I would think the FAA and other organizations want a standard-compliant DBMS system that knows how to convert a simple ISO-formatted valid string literal to a timestamp value in more than one variation of sql statement.

You can ignore this bug report and do whatever you want, just do not say this is an accepted, standard or desired behavior of the server or is by design. It's not by design that the error happens it is by faulty handling of the distinct keyword.

I think you have all the information you need to debate and resolve this issue. If you need any other information you can contact me and I will be happy to oblige.

Farid


On 6/4/2010 5:40 PM, Kevin Grittner wrote: 
I can't help but wonder why you resist using the standard syntax. 
The reason the standard exists is to help those trying to write
portable code, so they don't have to count on the vagaries of
"parallel evolution."

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