-----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 "tztest@pht.sql" tztest
$ grep -e '^[12]' tztest@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 tztest@pht.sql
[...]
psql:tztest@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-----
