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:t...@sss.pgh.pa.us] Sent: Thursday, December 03, 2009 12:40 AM To: Kevin Grittner Cc: Craig Ringer; Wagner, Kurt; pgsql-bugs@postgresql.org Subject: Re: [BUGS] BUG #5225: create table: cast necessary for constant?? "Kevin Grittner" <kevin.gritt...@wicourts.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 -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
