Skip site navigation (1) Skip section navigation (2)

Re: Substring auto trim

From: "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov>
To: "Charles O'Farrell" <charleso(at)gmail(dot)com>, "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: <pgsql-bugs(at)postgresql(dot)org>
Subject: Re: Substring auto trim
Date: 2010-01-13 16:01:51
Message-ID: 4B4D9A0F020000250002E432@gw.wicourts.gov (view raw or flat)
Thread:
Lists: pgsql-bugs
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
> instance,
> 
> postgres=# select substr('ab  '::char(4), 1, 4) = 'ab  '::char(4);
>  ?column? 
> ----------
>  t
> (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.
 
-Kevin

In response to

Responses

pgsql-bugs by date

Next:From: Greg StarkDate: 2010-01-13 16:48:10
Subject: Re: BUG #2197: PostgreSQL error- 'could not read block 0 of relation'
Previous:From: Tom LaneDate: 2010-01-13 15:35:35
Subject: Re: Substring auto trim

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group