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

<meta content="text/html; charset=ISO-8859-1"
 http-equiv="Content-Type">


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:
<blockquote cite="mid:4C092C650200002500032027@gw.wicourts.gov"
 type="cite">
  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 <font
 size="-1">between two databases
using ODBC drivers