Craig Ringer <cr...@postnewspapers.com.au> wrote: > On 3/12/2009 12:35 AM, Tom Lane wrote: >> You really ought to cast the 'I' to some specific type. > > It's usually neatest to do this by just explicitly identifying > the intended type in the first place, eg: > > > SELECT firmnr, > werknr, > TEXT 'I' as invper, > invnum > from .... ; > > ... which, IIRC, is the standard way to do it. I don't have a copy > to check against to be sure. > > Personally, I like the fact that Pg errs on the side of caution > here rather than guessing what you want. We should probably have some wiki page or something to which we can refer people when they raise this, which is bound to happen from time to time, since the PostgreSQL behavior is a deviation from the standard. Now, I've been persuaded that there are good reasons for the deviation, and that workarounds for code previously written to standard are relatively straightforward, but many people here lose sight of the fact that it *is* a deviation when replying to someone who's just run into it. 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. The length of a <character | string literal> is the number of <character representation>s | that it contains. Each <quote symbol> contained in <character | string literal> represents a single <quote> in both the value | and the length of the <character string literal>. The two | <quote>s contained in a <quote symbol> shall not be separated | by any <separator>. | | NOTE 72 * <character string literal>s are allowed to be | zero-length strings (i.e., to contain no characters) even | though it is not permitted to declare a <data type> that is | CHARACTER with <length> 0 (zero). 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. -Kevin
-- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs