Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> What's the data type of the value being compared to? I get, for
> postgres=# select substr('ab '::char(4), 1, 4) = 'ab '::char(4);
> (1 row)
This looks like another situation where we're running into trouble
because of non-standard behavior when people might be expecting
something consistent with other products and the explicit language
in the standard.
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).
Based on that, the cast of the literals to char(4) in your example
should not be needed. I don't know if there's any reasonable fix
or if this should be handled with a doc change or FAQ entry.
In response to
pgsql-bugs by date
|Next:||From: Greg Stark||Date: 2010-01-13 16:48:10|
|Subject: Re: BUG #2197: PostgreSQL error- 'could not read block 0 of relation'|
|Previous:||From: Tom Lane||Date: 2010-01-13 15:35:35|
|Subject: Re: Substring auto trim |