Thread: date_trunc check constraint causes errors when restoring in a db with a different time zone
date_trunc check constraint causes errors when restoring in a db with a different time zone
From
Anthony Manfredi
Date:
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
Re: date_trunc check constraint causes errors when restoring in a db with a different time zone
From
Denish Patel
Date:
Anthony,
As per my knowledge, this can't work on pg84 dump/restore too. You should
match timezone on restoring database with the dump database to load it
successfully. You can set at user level or database level.
On restoring database:
ALTER DATABASE test_db_2 SET TIMEZONE TO 'UTC';
OR
ALTER USER postgres SET TIMEZONE TO 'UTC';
Hope , it will help.
On Sat, Jan 15, 2011 at 10:50 AM, Anthony Manfredi <amanfredi@gilt.com>wrote:
> 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
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>
--
Denish Patel,
OmniTi Computer Consulting Inc.
Database Administrator,
Phone: 443.325.1357 x 232
Re: date_trunc check constraint causes errors when restoring in a db with a different time zone
From
Anthony Manfredi
Date:
Thanks, that is helpful. There was a separate reason it didn't appear to be happening before the 8.4 upgrade. I couldn't find this mentioned in the documentation anywhere. Should it be included in the pg_restore section? -Anthony On Sun, Jan 16, 2011 at 11:06 AM, Denish Patel <denish@omniti.com> wrote: > Anthony, > As per my knowledge, this can't work on pg84 dump/restore too. You should > match timezone on restoring database with the dump database to load it > successfully. You can set at user level or database level. > =A0On restoring database: > =A0=A0 ALTER DATABASE=A0test_db_2 SET TIMEZONE TO 'UTC'; > =A0=A0 =A0OR > =A0=A0 ALTER USER postgres=A0SET TIMEZONE TO 'UTC'; > Hope , it will help.