<meta content="text/html; charset=ISO-8859-1"
http-equiv="Content-Type">
On 6/4/2010 11:53 AM, Tom Lane wrote:
DISTINCT forces
the parser to assign a data type to the constants (otherwise there
is no way to understand what duplicate-elimination means) and what
it will fall back to is "text"
I am including the column list for the insert, so parser knows col2
data type is TIMESTAMP and it has to convert from text to timestamp to
do the insert.
It should be able to do that without generating an error. It is the
same select list, the same data types, nothing has changed except using
the 'distinct' keyword to eliminate duplicates. The parse behavior
after duplicates have been eliminated should be the same as when
'distinct' is not used.
Whether 'distinct' is used or not should not affect the semantics of
the insert statement (it should only remove duplicate rows).
I have used this statement in Firebrid, MS SQL Server, Oracle, MySQL,
SQLAnywhere, DB2, Derby, Informix, etc, and all of them do not generate
an error
because I need to use 'distinct' to eliminate duplicates from being
inserted.
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.
I guess, what I am saying, is that what the parser is doing is not the
desired behavior. I understand there are technical things going on
behind
the scene, but that's what needs to be fixed to ensure PostgreSQL
compatibility with SQL standard and interoperability with generic sql
statements.
best regards,
Farid
On 6/4/2010 11:57 AM, Kevin Grittner wrote:
<blockquote cite="mid:4C08DC140200002500031F7C@gw.wicourts.gov"
type="cite">
"Farid Zidan" <farid@zidsoft.com> wrote:
insert into test_insert
(col1, col2)
select distinct
'b',
'2010-04-30 00:00:00'
ERROR: column "col2" is of type timestamp without time zone but
expression is of type text
LINE 16: '2010-04-30 00:00:00'
^
HINT: You will need to rewrite or cast the expression.
Try using a timestamp literal instead of a bare literal:
insert into test_insert
(col1, col2)
select distinct
'b',
timestamp '2010-04-30 00:00:00'
This is actually working as intended in all the cases you showed, so
it isn't a bug. If we were strictly complying with the SQL
standard, your first example would also fail, but we are more
lenient than the standard where we can be, to allow an unadorned
literal to be an UNKNOWN type until something causes it to be
resolved, to allow people to omit the type decoration in many cases.
To determine that something is a distinct value, you have to
determine a type for it (otherwise you won't know if '2010-04-30
00:00:00' is the same as '2010-04-30 00:00:00.0', for example), so
if you don't tell it otherwise, it will assume text -- leading to
the behavior you saw.
-Kevin
--
Signature
www.zidsoft.com
CompareData: compare
and synchronize SQL DBMS data visually <font
size="-1">between two databases
using ODBC drivers