Skip site navigation (1) Skip section navigation (2)

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

From: Craig Ringer <craig(at)postnewspapers(dot)com(dot)au>
To: Dimitri Fontaine <dfontaine(at)hi-media(dot)com>
Cc: Farid Zidan <farid(at)zidsoft(dot)com>, Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov>, pgsql-bugs(at)postgresql(dot)org
Subject: Re: Re: BUG #5490: Using distinct for select list causes insert of timestamp string literal to fail
Date: 2010-06-07 02:57:26
Message-ID: 4C0C6016.7070900@postnewspapers.com.au (view raw or flat)
Thread:
Lists: pgsql-bugs
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

In response to

Responses

pgsql-bugs by date

Next:From: Craig RingerDate: 2010-06-07 03:17:07
Subject: Re: BUG #5490: Using distinct for select list causes insert of timestamp string literal to fail
Previous:From: Tom LaneDate: 2010-06-07 01:50:03
Subject: Re: BUG #5488: pg_dump does not quote column names -> pg_restore may fail when upgrading

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group