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

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(at)gilt(dot)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(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
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs(at)postgresql(dot)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