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

On 7/06/2010 3:51 AM, Dimitri Fontaine wrote:

> - other products are happy to solve the DISTINCT restriction without
> any hint as far as what the datatype really is

... and so is Pg. That's not the problem; Pg complains after resolving
the SELECT DISTINCT, when it finds that it's trying to insert values of
type 'text' into a column with type 'timestamp'.

You'll get exactly the same error if you replace the OP's SELECT
DISTINCT subquery with a VALUES list that explicitly specifies TEXT type.

The other clue as to what's happening is that if you run the SELECT part
of the query standalone, it executes fine, treating the passed values as
'text'.

Personally, I do think this is a bit of a wart. I know why the explicit
casts around text were removed, but in the case of INSERT I'm not sure
the current behaviour is desirable.

I initially thought the OP was asking for Pg to infer the type of the
timestamp literals from the surrounding INSERT, and for that reason was
very much against the idea. After realizing that what they really expect
is for the SELECT to interpret the literals as 'text' (just as it does)
then Pg to implicitly cast the 'text' query results to 'timestamp', I
can see why they want it and why they're frustrated with the current
behaviour.

> - the error message is perfectly clear about what PostgreSQL needs from
> you

Apparently not, as you seem to have misunderstood it ;-)

> - the reason why PostgreSQL wants you to give it details is clear to:
> what means DISTINCT depends on the datatype, you can easily have two
> different text representations of the same timestamptz, for example

That's not why Pg reports an error. If it was, then the following query
would not be legal:

SELECT DISTINCT x.* FROM (VALUES ('a'),('b'),('c')) AS x;

... since there's no explicit type info provided.

Pg follows the SQL rules and interprets literals as text if there's no
explicit type info provided and no immediate context that requires a
particular type. So the above work fine, treating 'a', 'b', and 'c' as
if they were written:

(TEXT 'a'), (TEXT 'b'), (TEXT 'c')

> - it could be considered a possible area of improvement in the system
> that the SELECT part of the INSERT INTO ... SELECT ... could
> determine the SELECT output columns type by looking at the INSERT
> target table definition

I'd say that'd be a ghastly "solution". It'd change the meaning of the
SELECT based on the surrounding INSERT. Imagine trying to figure out
what was going on with a query that wasn't doing what you wanted when
you couldn't run it standalone and know the results were the same!

--
Craig Ringer