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@mercury:[~]$ createdb test_db
amanfredi@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@mercury:[~]$ pg_dump -Fc -o -x test_db > test_db_dump.dmp
amanfredi@mercury:[~]$ createdb test_db_2
amanfredi@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@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
