Обсуждение: 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"