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
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. |