Re: BUG #5225: create table: cast necessary for constant??

Thanks Tom and Kevin

for your detailed explanation. Even if I know now there is no chance of
changing it I'd like you to consider following fact:

when writing a character constant elsewhere
then at first it is interpreted as character constant - right?
then it is casted to the desired type

e.g. SELECT... FROM ... WHERE now() > '2009-12-03'

at first the input is accepted as character literal
(@Tom: in no way the literal is automatically interpreted as a user
defined data type)
then the literal is casted to timestamp
then it is compared.

This behaviour you can see when typing a wrong timestamp:
ERROR: invalid input syntax for type timestamp with time zone:
"2009-12#03" [SQL State=22007]
-> this is a message created by the data type casting

setting the column type to unknown will deactivate all automatic type
casts available.
For me it was not logical (but errornous) to not interpret the literal
as

please refer to following statement:
CREATE TEMP TABLE blabla AS SELECT now() AS timecol, 0 AS intcol, 0.0 AS
deccol, 'I' AS CHARCOL FROM mytable;

Postgres decided to define deccol as numeric even not knowing the exact
type. But setting it to numeric it allows further processing. Only
charcol is unknown which causes problems in further processing.

you see what I mean?

thank you and kind regards

Kurt

-----Original Message-----
From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
Sent: Thursday, December 03, 2009 12:40 AM
To: Kevin Grittner
Cc: Craig Ringer; Wagner, Kurt; pgsql-bugs(at)postgresql(dot)org
Subject: Re: [BUGS] BUG #5225: create table: cast necessary for
constant??

"Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov> writes:
> Quoting from section 5.3 of "WG3:HBA-003 H2-2003-305 August, 2003
> (ISO-ANSI Working Draft) Foundation (SQL/Foundation)":
> | 13) The declared type of a <character string literal> is
> | fixed-length character string.
> Treating an otherwise unadorned set of characters between two
> apostrophes as anything except a character string literal of type
> CHARACTER with a length calculated per the above violates the
> standard. Rather than pretending otherwise, we should be prepared
> to explain the reasons for the deviation, describe what the
> PostgreSQL behavior *is*, and justify the deviation.

Sorry about that --- I had confused this case with that of a bare NULL
literal, which Postgres treats the same as an unadorned string literal
for type determination purposes. You're right that the spec treats
them differently. This is feasible for the spec's purposes because
it has such a paucity of data types. Also, I believe that the spec
expects you to explicitly mark literals that aren't to be treated
as plain strings, ie, in something like
TIMESTAMP '2009-12-02 18:28:58'
you're not really supposed to omit the word TIMESTAMP.

Postgres has a whole lot of datatypes, including user-added ones, and
most of them share the unadorned string literal as the base case for
constants. Giving preference to CHARACTER would make that machinery
a lot less pleasant to use.

regards, tom lane