Thread: BUG?: timestamp without TZ created as timestamp *with* TZ
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?
Did I miss something?
Here my SQL:
create table MEMBERS (
id serial primary key,
login text unique not null,
first_name char(64) not null,
last_name char(64) not null,
first_name_kana char(64) not null,
last_name_kana char(64) not null,
primary_email text not null,
pw_hash text not null,
membership_type integer references MEMBERSHIP_TYPES(id),
sex char(1) ,
dob date ,
payment_type integer references PAYMENT_TYPES(id),
last_login timestamp without time zone [0],
disabled boolean default true
);
I then do a "\d members" and get:
Column | Type |
Modifiers
-----------------+----------------------------+----------------------------------------------------
id | integer | not null default
nextval('"members_id_seq"'::text)
login | text | not null
first_name | character(64) | not null
last_name | character(64) | not null
first_name_kana | character(64) | not null
last_name_kana | character(64) | not null
primary_email | text | not null
pw_hash | text | not null
membership_type | integer |
sex | character(1) |
dob | date |
payment_type | integer |
last_login | timestamp with time zone[] |
disabled | boolean | default 't'::bool
Primary key: members_pkey
Unique keys: members_login_key
Triggers: RI_ConstraintTrigger_2169213,
RI_ConstraintTrigger_2169219
Jean-Christian Imbeault <jc@mega-bucks.co.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
Hi Tom, On Thu, 2002-08-29 at 22:56, Tom Lane wrote: > 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. Just looking at the 7.3 docs... Is this right? Have the docs been updated yet? http://developer.postgresql.org/docs/postgres/datatype-datetime.html timestamp [ (p) ] without time zone timestamp [ (p) ] [ with time zone ] which implies to me that "timestamp" means "timestamp with time zone"??? Its also quite late here and I'm a bit star-eye'd so maybe I've got it all wrong. Thanks, Tom. -- Thomas O'Dowd. - Nooping - http://nooper.com tom@nooper.com - Testing - http://nooper.co.jp/labs
"Thomas O'Dowd" <tom@nooper.com> writes:
> On Thu, 2002-08-29 at 22:56, Tom Lane wrote:
>> In 7.3 "timestamp" will mean "timestamp without time zone" per spec.
> Just looking at the 7.3 docs... Is this right? Have the docs been
> updated yet?
Yes, and perhaps not. There's no mention in the release notes, for
example, which is A Bad Thing.
regards, tom lane
Tom Lane wrote:
>
>There's also a nasty little bug in the 7.2 system catalogs:
Is this error message related to that bug? The error make no sense, it's
complaining that the DB is expecting one data type (timstamp with TZ),
receiving it, but not happy with it ...
Warning: pg_exec() query failed: ERROR: column "last_login" is of type
'timestamp with time zone[]' but expression is of type 'timestamp with
time zone' You will need to rewrite or cast the expression in
/www/htdocs/jc/db_functions/db_functions.inc on line 509
CONNECTION: could not execute query (insert into members(first_name,
last_name, first_name_kana, last_name_kana, primary_email, pw_hash,
membership_type, sex, dob, payment_type, last_login)
values('2','1','1','2','','12tir.zIbWQ3c','','f','1920-10-29','',now()))
Jc
Jean-Christian Imbeault dijo: > Tom Lane wrote: > > > >There's also a nasty little bug in the 7.2 system catalogs: > > Is this error message related to that bug? The error make no sense, it's > complaining that the DB is expecting one data type (timstamp with TZ), > receiving it, but not happy with it ... Nope. Note that the column is of an array type. Probably the table declaration is an error, given the column name. > Warning: pg_exec() query failed: ERROR: column "last_login" is of type > 'timestamp with time zone[]' but expression is of type 'timestamp with > time zone' You will need to rewrite or cast the expression in -- Alvaro Herrera (<alvherre[a]atentus.com>) "Use it up, wear it out, make it do, or do without"