date_trunc check constraint causes errors when restoring in a db with a different time zone
От | Anthony Manfredi |
---|---|
Тема | date_trunc check constraint causes errors when restoring in a db with a different time zone |
Дата | |
Msg-id | AANLkTikam_iMyY79DJKofCPBDmZsdpOEuxH=cPWXHNy-@mail.gmail.com обсуждение исходный текст |
Ответы |
Re: date_trunc check constraint causes errors when restoring
in a db with a different time zone
|
Список | 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@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
В списке pgsql-bugs по дате отправления: