BUG #8170: alter user does not accept timestamp output format in certain datestyles and timezones.
The following bug has been logged on the website:
Bug reference: 8170
Logged by: Chris Travers
Email address: chris.travers@gmail.com
PostgreSQL version: 9.2.4
Operating system: Debian Linux
Description:
I have a pl/pgsql function which calculates at imestamp and alters a user's
password to be valid for 24 hours pending a password change. When the
datestyle and timezone are set to certain settings this throws an
exception.
Here is an approximation without plpgsql:
db=# show timezone;
TimeZone
--------------
Asia/Jakarta
(1 row)
db=# show datestyle;
DateStyle
---------------
Postgres, DMY
(1 row)
db=# select now();
now
-------------------------------------
Mon 20 May 11:39:24.273508 2013 WIT
(1 row)
db=# select 'ALTER USER ' || quote_ident('chris') || ' with valid until ' ||
quote_literal(now() + '1 day');
?column?
------------------------------------------------------------------------
ALTER USER chris with valid until 'Tue 21 May 11:41:14.58554 2013 WIT'
(1 row)
db=# ALTER USER chris with valid until 'Tue 21 May 11:41:14.58554 2013
WIT';
ERROR: invalid input syntax for type timestamp with time zone: "Tue 21 May
11:41:14.58554 2013 WIT"
This worked before with different timezones with the same datestyle. Why is
this failing?
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
chris.travers@gmail.com writes:
db=# show timezone;
TimeZone
--------------
Asia/Jakarta
(1 row)
db=# select now();
now
-------------------------------------
Mon 20 May 11:39:24.273508 2013 WIT
(1 row)
db=# ALTER USER chris with valid until 'Tue 21 May 11:41:14.58554 2013 WIT';
ERROR: invalid input syntax for type timestamp with time zone: "Tue 21 May
11:41:14.58554 2013 WIT"
This worked before with different timezones with the same datestyle. Why is
this failing?
You need to add WIT to the timezone abbreviation list to allow it to be
used as input:
http://www.postgresql.org/docs/9.2/static/datetime-config-files.html
Or perhaps better, use the ISO datestyle to eliminate the whole issue of
timezone abbreviations.
regards, tom lane
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs