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

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

From: Greg Stark <gsstark(at)mit(dot)edu>
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: BUG #5490: Using distinct for select list causes insert of timestamp string literal to fail
Date: 2010-06-04 20:18:24
Message-ID: AANLkTilLvx4m4TlHxeFERL60Xubiz0IhTjUXqTkoH6Le@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-bugs
On Fri, Jun 4, 2010 at 7:18 PM, Farid Zidan <farid(at)zidsoft(dot)com> wrote:
> If a simple SQL statement works on 9+ different databases

For what it's worth are you sure it works as you expect in these other
databases?

I suspect what they're doing is doing a DISTINCT of the text values
and then converting the results. That's not the same as what you're
suggesting it do (insert distinct timestamp values) since different
text values can represent the same timestamp. For example look at what
this does:

 select cast(x as timestamp with time zone) from (select distinct x
from (values ('2010-01-01 12:00PM UTC'), ('2010-01-01 7:00AM EST')) as
x(x)) as y;

If you inserted those values into a table with a timestamp with time
zone column you would get duplicate values even with the distinct.

This is the problem with depending on non-standard extensions. You're
never really sure that they're working. They be working on some
systems but doing something unexpected on other systems.

-- 
greg

In response to

Responses

pgsql-bugs by date

Next:From: Robert HaasDate: 2010-06-04 20:41:08
Subject: Re: superuser unable to modify settings of a system table
Previous:From: Bruce MomjianDate: 2010-06-04 19:19:42
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