date_trunc check constraint causes errors when restoring in a db with a different time zone

From: Anthony Manfredi <amanfredi(at)gilt(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: date_trunc check constraint causes errors when restoring in a db with a different time zone
Date: 2011-01-15 15:50:23
Message-ID: AANLkTikam_iMyY79DJKofCPBDmZsdpOEuxH=cPWXHNy-@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

When I create a database dump from a database with time zone = UTC (my
production machine) and attempt to load it in a database with the
'US/Eastern' time zone (my development machine), pg_restore reports
that the dump violates a check constraint. The constraint uses
date_trunc('day', <timestamptz>) to ensure that all values in the
table are truncated to the same precision. I did not see this error
before upgrading from to Postgresql 9.0 from 8.4.

The following commands will reproduce the error:

amanfredi(at)mercury:[~]$ createdb test_db
amanfredi(at)mercury:[~]$ psql test_db
psql (9.0.2)
Type "help" for help.

test_db=# select version();

version
------------------------------------------------------------------------------------------------------------------------------------------
PostgreSQL 9.0.2 on x86_64-apple-darwin10.5.0, compiled by GCC
i686-apple-darwin10-gcc-4.2.1 (GCC) 4.2.1 (Apple Inc. build 5664),
64-bit
(1 row)

test_db=# set time zone 0;
SET
test_db=# show time zone;
TimeZone
----------
00:00:00
(1 row)

test_db=# create table test_table ( start_time timestamp with time
zone NOT NULL, CONSTRAINT time_days_start_time_ck CHECK ((start_time =
date_trunc('day'::text, start_time))) );
CREATE TABLE
test_db=# insert into test_table (start_time) values
(date_trunc('day', 'Jan 15, 2010'::timestamptz));
INSERT 0 1
test_db=# select * from test_table;
start_time
------------------------
2010-01-15 00:00:00+00
(1 row)

test_db=# \q
amanfredi(at)mercury:[~]$ pg_dump -Fc -o -x test_db > test_db_dump.dmp
amanfredi(at)mercury:[~]$ createdb test_db_2
amanfredi(at)mercury:[~]$ psql test_db_2
psql (9.0.2)
Type "help" for help.

test_db_2=# show time zone;
TimeZone
------------
US/Eastern
(1 row)

test_db_2=# \q
amanfredi(at)mercury:[~]$ pg_restore -d test_db_2 test_db_dump.dmp
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 1778; 0 505055 TABLE
DATA test_table amanfredi
pg_restore: [archiver (db)] COPY failed: ERROR: new row for relation
"test_table" violates check constraint "time_days_start_time_ck"
CONTEXT: COPY test_table, line 1: "2010-01-14 19:00:00-05"
WARNING: errors ignored on restore: 1

Best,
Anthony

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Karsten Loesing 2011-01-15 17:12:11 BUG #5840: Updating arrays using subscripted assignment results in uninitialized values
Previous Message Tom Lane 2011-01-15 15:45:03 Re: Problems with adding a is not null to a query.