Re: BUG #6391: insert does not insert correct value

Following the example provided, this still appears to be a bug. The results are very different when we run the example. We did notice that although now() failed, changing this to 'now()'::date works (using single quotes around now()). I will be working with our administrators to review patches and version levels.

Also, apparently we are not truly a Postgres database, but EMC's Greenplum database. I will be working with our admins and EMC, unless someone in Postgres can point out a specific version that resolved this issue.

drop table if exists t;
create table t
(id int primary key,
d1 date not null,
d2 date not null);

insert into t
values
(1, now(), current_date),
(2, localtimestamp::date, date_in(date_out(current_date))),
(3, 'now()'::date, clock_timestamp()::DATE);

select * from t order by 1;

id | d1 | d2
----+------------+------------
1 | 1999-12-31 | 1999-12-31
2 | 1999-12-31 | 1999-12-31
3 | 2012-01-11 | 2012-01-11

Additional information - select version();
"PostgreSQL 8.2.15 (Greenplum Database 4.1.1.5 build 1) on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.4.2 compiled on Sep 20 2011 11:09:08"

John Udick | Enterprise Data Warehouse Development

-----Original Message-----
From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
Sent: Wednesday, January 11, 2012 8:21 AM
To: John Udick
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: [BUGS] BUG #6391: insert does not insert correct value

john(dot)udick(at)zionsbancorp(dot)com writes:
> I would expect that at the time/date of the of now() and clock_timestamp()
> to be equal; which they are.

Well, they are not in general, but that doesn't appear to be your
problem. You have not shown us a reproducible test case, but I wonder
whether your table has a trigger that is doing date_trunc('year',...)
or something like that on the column value.

regards, tom lane

======================================================================
THIS ELECTRONIC MESSAGE, INCLUDING ANY ACCOMPANYING DOCUMENTS, IS CONFIDENTIAL and may contain information that is privileged and exempt from disclosure under applicable law. If you are neither the intended recipient nor responsible for delivering the message to the intended recipient, please note that any dissemination, distribution, copying or the taking of any action in reliance upon the message is strictly prohibited. If you have received this communication in error, please notify the sender immediately. Thank you.