Re: [BUGS] BUG #5490: Using distinct for select list causes insert of timestamp string literal to fail

[Farid Zidan]

Title: Signature

Hello Kevin, My bug report is about using 'distinct' in the select list which is causing a side-effect. That's why I classify this as a bug. Distinct should not have unintended side-effects. This side-effect is implementation-dependent and is manifested in the current PostgreSQL query processing but can be eliminated by appropriately handling the distinct keyword and does not have to occur. The ISO-datetime string literal format I am using the most general/standard for datetime/timestamp and is not the issue here. The 'distinct' keyword is causing the error. Farid On 6/4/2010 12:52 PM, Kevin Grittner wrote: Farid Zidan <fa...@zidsoft.com> wrote: If we were strictly complying with the SQL standard, Considering the statement works in all the 9 DBMS systems+ that I have tested so far as mentioned above, I would say PostgreSQL is not compliant with SQL standard in this regard. The SQL standard is a document published by the International Standards Organization (ISO) and also adopted by the American National Standards Institute (ANSI). Those documents don't require a query in either of the forms you presented to work. Because of the convenience factor, most database products have non-standard extensions to omit type specification in some places. PostgreSQL's extensions are oriented more toward user-installable data types (such as geometric shapes or global coordinates), so the particulars of our non-standard extensions differ so that use of those features is as easy as practicable. That does result in some non-standard extensions which work in other products not working in PostgreSQL. I think you'll find that the syntax I suggested (using the standard timestamp literal instead of a bare character string literal) will work in all of the databases you mentioned; if you want portable code, it is best to follow the standard rather than some inferred popular convention. I hope this helps. -Kevin -- www.zidsoft.com CompareData:  compare and synchronize SQL DBMS data visually between two databases using ODBC drivers