I just came across this error that I need to account for after updating to
7.3.2:
Note: Prior to PostgreSQL 7.3, writing just timestamp was equivalent to
timestamp with time zone. This was changed for SQL spec compliance.
(from
http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=datatype-datetime.html)
test=# create table test2 (id serial,date_entered timestamp with time zone);
NOTICE: CREATE TABLE will create implicit sequence 'test2_id_seq' for SERIAL
column 'test2.id'
CREATE TABLE
test=# \d test2
Table "public.test2"
Column | Type | Modifiers
--------------+--------------------------+-------------------------------------------------------
id | integer | not null default
nextval('public.test2_id_seq'::text)
date_entered | timestamp with time zone |
test=# insert into test2 values (4,'04/01/2003'::date);
INSERT 145267 1
test=# insert into test2 values (3,'04/01/2003'::timestamp);
INSERT 145268 1
test=# select * from test2;
id | date_entered
----+------------------------
4 | 2003-03-31 23:59:00-08
3 | 2003-03-31 23:59:00-08
This does not affect tables with 'timestamp without time zone'. I find it
interesting that it changes the time to 1 minute before midnight. Not a big
change to fix this in scripts, but hopefully others will become aware of this
if/when they upgrade to 7.3.
-doug