Craig Ringer <craig@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