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:

"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 between two databases using ODBC drivers