agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Kevin Grittner <kgrittn@ymail.com>
To: Bruce Momjian <bruce@momjian.us>
To: Thomas Fanghaenel <tfanghaenel@salesforce.com>
Cc: PostgreSQL-development <pgsql-hackers@postgreSQL.org>
Subject: Re: [SQL] Comparison semantics of CHAR data type
Date: Fri, 11 Oct 2013 13:58:50 -0700 (PDT)
Message-ID: <1381525130.59803.YahooMailNeo@web162902.mail.bf1.yahoo.com> (raw)
In-Reply-To: <20131011194437.GA3614@momjian.us>
References: <CAK+WP1xdmyswEehMuetNztM4H199Z1w9KWRHVMKzyyFM+hV=zA@mail.gmail.com>
<20131011194437.GA3614@momjian.us>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>
Bruce Momjian <bruce@momjian.us> wrote:
> Thomas Fanghaenel wrote:
>> I was wondering about the proper semantics of CHAR comparisons in some corner
>> cases that involve control characters with values that are less than 0x20
>> (space).
What matters in general isn't where the characters fall when
comparing individual bytes, but how the strings containing them
sort according to the applicable collation. That said, my
recollection of the spec is that when two CHAR(n) values are
compared, the shorter should be blank-padded before making the
comparison. *That* said, I think the general advice is to stay
away from CHAR(n) in favor or VARCHAR(n) or TEXT, and I think that
is good advice.
> I am sorry for this long email, but I would be interested to see what
> other hackers think about this issue.
Since we only have the CHAR(n) type to improve compliance with the
SQL specification, and we don't generally encourage its use, I
think we should fix any non-compliant behavior. That seems to mean
that if you take two CHAR values and compare them, it should give
the same result as comparing the same two values as VARCHAR using
the same collation with the shorter value padded with spaces.
So this is correct:
test=# select 'ab'::char(3) collate "en_US" < E'ab\n'::char(3) collate "en_US";
?column?
----------
t
(1 row)
... because it matches:
test=# select 'ab '::varchar(3) collate "en_US" < E'ab\n'::varchar(3) collate "en_US";
?column?
----------
t
(1 row)
But this is incorrect:
test=# select 'ab'::char(3) collate "C" < E'ab\n'::char(3) collate "C";
?column?
----------
t
(1 row)
... because it doesn't match:
test=# select 'ab '::varchar(3) collate "C" < E'ab\n'::varchar(3) collate "C";
?column?
----------
f
(1 row)
Of course, I have no skin in the game, because it took me about two
weeks after my first time converting a database with CHAR columns
to PostgreSQL to change them all to VARCHAR, and do that as part of
all future conversions.
--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
view thread (8+ messages) latest in thread
Message-ID: <1381525130.59803.YahooMailNeo@web162902.mail.bf1.yahoo.com>
Permalink: ../1381525130.59803.YahooMailNeo@web162902.mail.bf1.yahoo.com/
Also on: postgresql.org/message-id/1381525130.59803.YahooMailNeo@web162902.mail.bf1.yahoo.com
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: kgrittn@ymail.com, bruce@momjian.us, tfanghaenel@salesforce.com, pgsql-hackers@postgreSQL.org
Subject: Re: [SQL] Comparison semantics of CHAR data type
In-Reply-To: <1381525130.59803.YahooMailNeo@web162902.mail.bf1.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox