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

Re: Re: BUG #5490: INSERT doesn't force cast from text to timestamp

From: Craig Ringer <craig(at)postnewspapers(dot)com(dot)au>
To: Farid Zidan <farid(at)zidsoft(dot)com>
Cc: Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov>, pgsql-bugs(at)postgresql(dot)org
Subject: Re: Re: BUG #5490: INSERT doesn't force cast from text to timestamp
Date: 2010-06-06 03:09:24
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-bugs
On 5/06/2010 9:39 PM, Farid Zidan wrote:
> Craig
> I am not asking you to re-write my sql so the bug will not show. I am
> presenting you with sql that fails and shows the bug.

Before writing this off completely, please read on. I suspect I may've 
misunderstood your argument, and on further examination think there's 
something that bears design consideration here. It's not a bug, as it's 
working exactly as designed, but it _is_ something where the design 
might benefit from a tweak.

In the mean time, you have a system that doesn't work how you want it do 
- design choice or bug, workaround or bug-hiding, the effect is the 
same. You must already be handing differing names of timestamp/datetime 
types in your DDL, so why not use the same mapping in your DML?

As for the root of the issue: Here's why implicitly casting those 
literals to timestamps would be a bad idea for solid, technical reasons:

- The semantics of a nested/sub query should not be affected by the 
calling context, ie the surrounding query. To have a function affected 
by what the caller is doing would be bizarre and confusing; so it is 
with a subquery. You cannot test functional units or rely on any kind of 
consistent behaviour if calling context changes callee behaviour.

- A SELECT as a value-supplier to an INSERT is a type of subquery

- If your SELECT ran differently in the context of the INSERT to how it 
ran standalone, that would not only be incredibly confusing but also 
clearly a bug.

... so we clearly can't use type information from the surrounding INSERT 
to determine the data type of the literals used in the SELECT. With no 
type information to the contrary they must be interpreted as text. So, 
after SELECT evaluation your query goes from:

insert into test_insert
(col1, col2)
select distinct
'2010-04-30 00:00:00';

to effectively:

insert into test_insert
(col1, col2)
values ('b'::text, '2010-04-30 00:00:00'::text);

Now, when executed with Pg both those queries result in:

ERROR:  column "col2" is of type timestamp without time zone but 
expression is of type text

showing that your issue isn't actually with DISTINCT at all, but with 
Pg's unwillingness to *implicitly* cast a value of explict text type to 
another type.

This is the real core of your complaint. You want Pg to cast from 'text' 
to 'timestamp' on INSERT. Pg doesn't. In  the general case (not specific 
to INSERT) there are some pretty good reasons for that, but for 
INSERTs... should it? In the context of an INSERT, where the desired 
data type is explicitly specified and obvious, I'm not sure, but I 
suspect that it should.

Anyone familiar with the details of the implicit-text-cast bugs want to 
pipe up with whether it'd be safe to force a cast in the context of an 

( As for why I adjusted your example: Your example query was 
unrealistically simplistic and clearly couldn't be what you were using 
in your app. Using 'DISTINCT' with one value is pointless. So, my 
example added some dummy values to illustrate why it might be unsafe to 
use it how you're trying to. As it turns out, in your app's case you can 
guarantee input formatting consistency, so it's safe for you so long as 
you stick very closely to timestamp formatting specifics, but I'm sure 
you can see that a database's behaviour depending on the formatting of 
timestamps is probably not something that most people with most apps 
would be happy with. Even if INSERT did convert the results of the 
SELECT DISTINCT subquery to timestamps, it'd still be pretty unsafe.)

Craig Ringer

In response to

pgsql-bugs by date

Next:From: Robert HaasDate: 2010-06-06 18:40:19
Subject: Re: BUG #5488: pg_dump does not quote column names -> pg_restore may fail when upgrading
Previous:From: Farid ZidanDate: 2010-06-06 01:12:00
Subject: Re: Re: BUG #5490: Using distinct for select list causes insert of timestamp string literal to fail

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