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

[Farid Zidan]

Hello Kevin, I strongly disagree with your analysis of this issue. Like I said, this syntax works with 9 different databases, so obviously whatever PosgreSQL query procesor is doing in this case is not the desired behavior. To ensure PosgreSQL success, the query processor must behave in a compliant manner with established standards whether those standards are set by SQL ISO specs or are de facto standards. It is too much asking developers to change their sql to overcome implementation-dependent side-effects of PostgreSQL query processor. If a simple SQL statement works on 9+ different databases, then it should also work in PostreSQL with no need for developers to special-code for PostgreSQL. Very basic feature is converting a string literal to a datetime/timestamp value and developers should not do any special coding to accomplish this simple conversion. '2010-04-30 00:00:00' should convert to timestamp in PostgreSQL with no other flags or syntax decoration (it already does except when 'distinct' is used). Compatibility is very high on desired features for a DBMS and is a requirement for smooth porting of applications from other databases to PostreSQL and cross-dbms applications. It really boils down to making it work, technical details are what developers love and I am sure PostgreSQL developers can make this simple sql insert work on PostreSQL just like all the other developers have done for the other DBMSs. Anyway, I have reported this issue because I encountered it and it negatively impacts my project. I don't expect it to be fixed right now, that's something that PostgreSQL developers can debate and prioritize. I only ask that this issue is identified, since it does not work in my case when the target dbms is PostgreSQL and I am sure it can impact other developers projects and it would need to be addressed at some point in the future with a solution where it just work like it does in all the other DBMSs. Farid On 6/4/2010 1:36 PM, Kevin Grittner wrote: Farid Zidan <fa...@zidsoft.com> wrote: can be eliminated by appropriately handling the distinct keyword and does not have to occur. Based on previous discussions around our approaching data types, I don't think any of the regular PostgreSQL developers are likely to agree with you; but if you see a way to make it work, feel free to submit a patch. See this page for the process: http://wiki.postgresql.org/wiki/Submitting_a_Patch The ISO-datetime string literal format I am using the most general/standard for datetime/timestamp and is not the issue here. The format in your string literal is the portable one; however, a timestamp literal requires the TIMESTAMP keyword ahead of the string literal, which you have chosen to omit. Did you try the query with a proper timestamp literal, as I suggested, against all these databases? If using standard syntax works, why not use it? The 'distinct' keyword is causing the error. No, non-standard syntax is causing the error in the case of DISTINCT, because our extension to the standard does not cover that case, even though it covers the other. There are good reasons for that, which you'll probably discover in short order if you work on a patch for the issue. -Kevin -- www.zidsoft.com CompareData:  compare and synchronize SQL DBMS data visually between two databases using ODBC drivers