Skip site navigation (1) Skip section navigation (2)

Re: BUG?: timestamp without TZ created as timestamp *with* TZ

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Jean-Christian Imbeault <jc(at)mega-bucks(dot)co(dot)jp>
Cc: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Re: BUG?: timestamp without TZ created as timestamp *with* TZ
Date: 2002-08-29 13:56:44
Message-ID: 25828.1030629404@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-general
Jean-Christian Imbeault <jc(at)mega-bucks(dot)co(dot)jp> writes:
> It seems that even though I declare a columns to be of type timestamp 
> without time zone it gets created as a data type *with* a time zone?

That is the behavior in 7.2; it's a transient state to help people
migrate from our old not-very-SQL-compliant datatype names.

In 7.3 "timestamp" will mean "timestamp without time zone" per spec.

> Here my SQL:
> create table MEMBERS (
> last_login              timestamp without time zone [0],
> );

> I then do a "\d members" and get:

>   last_login      | timestamp with time zone[] |

There's also a nasty little bug in the 7.2 system catalogs: the
array-of-timestamp-without-tz datatype is mistakenly linked to the
timestamp-with-tz element type.  You can fix this with a quick UPDATE to
pg_type; in the source code the correction looks like

403c403
< DATA(insert OID = 1115 ( _timestamp  PGUID    -1 -1 f b t \054 0      1184 array_in array_out array_in array_out d x _null_ ));
---
> DATA(insert OID = 1115 ( _timestamp  PGUID    -1 -1 f b t \054 0      1114 array_in array_out array_in array_out d x _null_ ));

The actual SQL command to give is in the archives somewhere, if you
need more help.

			regards, tom lane

In response to

Responses

pgsql-general by date

Next:From: Stephan SzaboDate: 2002-08-29 13:57:09
Subject: Re: Deleting foreign key constraints
Previous:From: Jules AlbertsDate: 2002-08-29 13:47:14
Subject: Re: Securing sensitive information

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group