Time zone HADT timestamp syntax error in trigger

Started by John Smithusalmost 17 years ago2 messagesgeneral
Jump to latest
#1John Smithus
smithus.john@gmail.com

Greetings!

I'm encountering an "invalid input syntax for type timestamp" error in
a trigger.

The server is running PostgreSQL 8.3.7 on an AMD64 Gentoo Linux
machine. The system time zone is set to 'America/Adak' and datestyle
is set to 'sql, mdy' in postgresql.conf.

The target column type is "timestamp (0) with time zone", but for
testing, I am able to duplicate the results using the trigger example
located at the following location:

http://www.postgresql.org/docs/8.3/interactive/plpgsql-trigger.html#PLPGSQL-TRIGGER-EXAMPLE

I create a new database, create the example table, function, and
trigger, and then perform the following query is psql:

INSERT INTO emp (empname, salary) VALUES ('John', '3000');

It immediately returns with:

ERROR: invalid input syntax for type timestamp: "04/30/2009
08:48:05.760442 HADT"
CONTEXT: PL/pgSQL function "emp_stamp" line 16 at assignment

As I mentioned, I'm actually targeting a column type of "timestamp (0)
with time zone", but I'd like to know what modification should be done
to that trigger example to allow the assignment to succeed.

Any advice is greatly appreciated.

Thank you very much!

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: John Smithus (#1)
Re: Time zone HADT timestamp syntax error in trigger

John Smithus <smithus.john@gmail.com> writes:

[ 'HADT' is not recognized as a timezone abbreviation ]
The server is running PostgreSQL 8.3.7 on an AMD64 Gentoo Linux
machine. The system time zone is set to 'America/Adak' and datestyle
is set to 'sql, mdy' in postgresql.conf.

Not every timezone abbreviation in the world is recognized by default.
You'll want to add HADT, as well as whatever the standard-time abbreviation
for that zone is, to Postgres's list of recognized abbreviations. See
http://www.postgresql.org/docs/8.3/static/datetime-config-files.html

regards, tom lane