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

[Farid Zidan]

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" <fa...@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 -- www.zidsoft.com CompareData:  compare and synchronize SQL DBMS data visually between two databases using ODBC drivers