varchar sort ordering ignore blanks

From: Luca Arzeni <l(dot)arzeni(at)amadego(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: varchar sort ordering ignore blanks
Date: 2008-01-15 15:32:12
Message-ID: 200801151632.12744.l.arzeni@amadego.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi there,
I have a table with a single column, pk of varchar type

The table contains few names, say:

XXXX A
XXXX C
XXXXB

In the first two records there is a between the XXXX and the following letter
A and C while, the third one has a B immediately following the XXXX (without
blanks).

In postgres 7.4.7 (debian sarge), if I issue a select to sort the record I
(correctly) obtain:
XXXX A
XXXX C
XXXXB

In postgres 8.1.9 (debian etch), if I issue a select to sort the record I
(mistakenly) obtain:
XXXX A
XXXXB
XXXX C

That is: the sort order in postgres 8.1.9 seems to ignore the blank.

In all cases I'm using locale LATIN9 during DB creation, but I tested also
with ASCII, UTF8 and LATIN1 encoding.

Can someone help me to get the correct order in postgres 8.1.9 ?

=== Sample code ===

CREATE TABLE t_table
(
c_column varchar(30) NOT NULL,
CONSTRAINT t_table_pk PRIMARY KEY (c_column)
)
WITHOUT OIDS;

INSERT INTO t_table(c_column) VALUES ('XXXX A');
INSERT INTO t_table(c_column) VALUES ('XXXXB');
INSERT INTO t_table(c_column) VALUES ('XXXX C');

select * from t_table order by c_column asc;

=============

Thanks, Luca Arzeni

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Vyacheslav Kalinin 2008-01-15 15:36:14 Prepared statement's planning
Previous Message Tom Lane 2008-01-15 15:32:10 Re: Patch for Statement.getGeneratedKeys()