I think that this example in the docs [1] is wrong:
"""
Values of type character are physically padded with spaces to the
specified width n, and are stored and displayed that way. However,
trailing spaces are treated as semantically insignificant and
disregarded when comparing two values of type character. In collations
where whitespace is significant, this behavior can produce unexpected
results, e.g. SELECT 'a '::CHAR(2) collate "C" < 'a\n'::CHAR(2)
returns true.
"""
Now, it is the case that the SQL statement will yield true:
postgres=# SELECT 'a '::CHAR(2) collate "C" < 'a\n'::CHAR(2);
?column?
----------
t
(1 row)
However, so does the same formulation with varchar, even though this
example is motivated by showing how char(n) differs from other
character types like varchar(n):
postgres=# SELECT 'a '::VARCHAR(2) collate "C" < 'a\n'::VARCHAR(2);
?column?
----------
t
(1 row)
I believe that the problem is that commit 8457d0bec did not correctly
transcribe the string constant with C-like escape notation from a C
code comment that it removed as redundant (it was roughly the same
example -- guess the simplification of the example went too far).
Attached patch fixes the documentation. When an "E" is added so the
C-like escape is correctly interpreted, the char(n) example continues
to yield true:
postgres=# SELECT 'a '::CHAR(2) collate "C" < E'a\n'::CHAR(2);
?column?
----------
t
(1 row)
But changing the cast to varchar will now yield a different result
(perhaps the intuitive result to those not familiar with char(n)
semantics):
postgres=# SELECT 'a '::VARCHAR(2) collate "C" < E'a\n'::VARCHAR(2);
?column?
----------
f
(1 row)
[1] http://www.postgresql.org/docs/devel/static/datatype-character.html
--
Peter Geoghegan
diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml
index 4d883ec..2456a50 100644
--- a/doc/src/sgml/datatype.sgml
+++ b/doc/src/sgml/datatype.sgml
@@ -1102,7 +1102,7 @@ SELECT '52093.89'::money::numeric::float8;
of type <type>character</type>. In collations where whitespace
is significant, this behavior can produce unexpected results,
e.g. <command>SELECT 'a '::CHAR(2) collate "C" <
- 'a\n'::CHAR(2)</command> returns true.
+ E'a\n'::CHAR(2)</command> returns true.
Trailing spaces are removed when converting a <type>character</type> value
to one of the other string types. Note that trailing spaces
<emphasis>are</> semantically significant in
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
