-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Hi,
I have the following strange effect, maybe someone has an idea on wether this is 'normal' or a bug: My platform: Ubuntu 12.04, PostgreSQL 9.2beta1, tried it with 9.1 with same result. postgresql.conf is stock, except for timezone, which is set to Asia/Manila. $ createdb -U postgres tztest $ psql tztest postgres tztest=# create table foo (bar int, baz timestamptz); CREATE TABLE tztest=# insert into foo values (1,'2012-05-01T14:00:00'); INSERT 0 1 tztest=# insert into foo values (2,'2012-05-01T14:00:00 bc'); INSERT 0 1 tztest=# select * from foo; bar | baz - -----+------------------------------ 1 | 2012-05-01 14:00:00+08 2 | 2012-05-01 14:00:00-15:56 BC Now that's strange already. Take a look upon this funny timezone offset. Let's backup this now. $ pg_dump -Fp -U postgres -x -O -a -f "tzt...@pht.sql" tztest $ grep -e '^[12]' tzt...@pht.sql 1 2012-05-01 14:00:00+08 2 2012-05-01 14:00:00-15:56 BC Now that's expected, it's the same as the output before. Let's restore it. $ psql tztest postgres tztest=# delete from foo; DELETE tztest=# \i tzt...@pht.sql [...] psql:tzt...@pht.sql:20: ERROR: time zone displacement out of range: "2012-05-01 14:00:00-15:56 BC" KONTEXT: COPY foo, line 2, column baz: "2012-05-01 14:00:00-15:56 BC" tztest=# It can't import because -15:56 is out of range for a timezone offset. But it's in the backup, so it should restore it, though, right? Doing this with any other format, using pg_restore, gives the same error. The only workaround I found was (without sacrificing functionality of date queries, that is) was to manually set the time zone to GMT+8 *prior* to running pg_dump. My question upon you is now: Is it my platform, which causes this, or is the constraint within the restore which is bad, or, which is my best bet, should pg_dump leave it's fingers off the time zone calculation and backup the data as if time zone 'UTC' was given within postgresql.conf? - -- cu, Patric -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.11 (GNU/Linux) Comment: GnuPT 2.5.2 iEYEARECAAYFAk/GjSMACgkQfGgGu8y7ypD6WwCgj0EPBp7RpQENNtZA4vvefooP 7DcAoNkIl3EkUnk1rYFigoMta4fm/6Q0 =TMAu -----END PGP SIGNATURE----- -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
