X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id C1093D1B50B for ; Wed, 17 Sep 2003 12:39:54 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 05828-03 for ; Wed, 17 Sep 2003 09:39:44 -0300 (ADT) Received: from bart.vegasys.net (bart.vegasys.net [213.180.65.17]) by svr1.postgresql.org (Postfix) with ESMTP id 4E22AD1B502 for ; Wed, 17 Sep 2003 09:39:40 -0300 (ADT) Received: from devo.se ([213.180.73.10]) by bart.vegasys.net (8.12.3/8.12.3/Debian-6.3) with ESMTP id h8HCdakx015340 for ; Wed, 17 Sep 2003 14:39:37 +0200 Received: from devo.se ([213.180.73.9]) by devo.se ([213.180.73.10]) with SMTP (MDaemon.Standard.v6.7.9.R) for ; Wed, 17 Sep 2003 14:39:47 +0100 Message-ID: <3F685600.3010606@devo.se> Date: Wed, 17 Sep 2003 14:39:28 +0200 From: =?ISO-8859-1?Q?Kristian_J=F6rg?= Organization: Devo IT AB User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.4) Gecko/20030624 Netscape/7.1 (ax) X-Accept-Language: en-us, en MIME-Version: 1.0 Cc: Richard Huxton , pgsql-sql@postgresql.org Subject: Re: Sort order with spaces? References: <3F682DB3.6080905@devo.se> <200309171144.02871.dev@archonet.com> <3F6853E4.3000604@devo.se> In-Reply-To: <3F6853E4.3000604@devo.se> Content-Type: multipart/alternative; boundary="------------090109010606080701080005" X-Authenticated-Sender: krjg@devo.se X-MDRemoteIP: 213.180.73.9 X-Return-Path: krjg@devo.se X-MDaemon-Deliver-To: pgsql-sql@postgresql.org X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200309/156 X-Sequence-Number: 15134 This is a multi-part message in MIME format. --------------090109010606080701080005 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 8bit Kristian Jörg wrote: > > > Richard Huxton wrote: > >>On Wednesday 17 September 2003 10:47, Kristian Jörg wrote: >> >> >>>Hello! >>> >>>I am having troubles with sort order in Postgres. It seems that space is >>>not handled at all? >>>For instance the following rows are sorted in MS SQL Server as: >>> >>>LUNDGREN >>>M L R >>>MACDOWELL >>>MUSCLE >>> >>>But in Postgres I get this order: >>> >>>LUNDGREN >>>MACDOWELL >>>M L R >>>MUSCLE >>> >>> >> >>Sort order depends upon your locale settings (specifically LC_COLLATE), which >>will have been set when you ran "initdb". Basically, sort orders for C , >>en_GB and fr will all be different. I'm guessing you expect "C" style >>sorting. >> >>Check the end of your postgresql.conf file to see what settings you currently >>have. >>See the manuals (Localization section) and list archives for plenty of >>details. >> >> > > Hi Richard! > > Unfortunately this does not seem to help! I dumped the database, > recreated the cluster with initdb with LC_COLLATE = C (and even tried > setting LC_CTYPE to C also), and restored the database. The same > ordering appears... > I did look through all the manuals and I noted that the method for > setting locale is different for 7.2 and 7.3, so I followed the 7.2 > docs. My postgres version is 7.2. > > The table keeps the data above in a column named "NORMTEXT" and my SQL > statement for the result above is: > select * from mytable where NORMTEXT >= 'LU' > Nothing out of the ordinary there.. > Any help on this matter is highly appreciated! Woops, a type above. My sql is of course: select * from mytable where normtext>= 'LU' order by normtext; > > Regards > Kristian > >-- > \\|// > (@ @) >+------ooO--(_)--Ooo----------+------------------------------+ >| Kristian Jörg | Phone: +46 54 153395 | >| Devo IT AB | Fax: +46 54 153389 | >| Box 533, SE-651 12 KARLSTAD | mailto:kristian.jorg@devo.se | >| Sweden | http://www.devo.se | >+-----------------------------+------------------------------+ > -- \\|// (@ @) +------ooO--(_)--Ooo----------+------------------------------+ | Kristian Jörg | Phone: +46 54 153395 | | Devo IT AB | Fax: +46 54 153389 | | Box 533, SE-651 12 KARLSTAD | mailto:kristian.jorg@devo.se | | Sweden | http://www.devo.se | +-----------------------------+------------------------------+ --------------090109010606080701080005 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit

Kristian Jörg wrote:


Richard Huxton wrote:
On Wednesday 17 September 2003 10:47, Kristian Jörg wrote:
  
Hello!

I am having troubles with sort order in Postgres. It seems that space is
not handled at all?
For instance the following rows are sorted in MS SQL Server as:

LUNDGREN
M L R
MACDOWELL
MUSCLE

But in Postgres I get this order:

LUNDGREN
MACDOWELL
M L R
MUSCLE
    

Sort order depends upon your locale settings (specifically LC_COLLATE), which 
will have been set when you ran "initdb". Basically, sort orders for C , 
en_GB and fr will all be different. I'm guessing you expect "C" style 
sorting.

Check the end of your postgresql.conf file to see what settings you currently 
have.
See the manuals (Localization section) and list archives for plenty of 
details.

Hi Richard!

Unfortunately this does not seem to help! I dumped the database, recreated the cluster with initdb with LC_COLLATE = C (and even tried setting LC_CTYPE to C also), and restored the database. The same ordering appears...
I did look through all the manuals and I noted that the method for setting locale is different for 7.2 and 7.3, so I followed the 7.2 docs. My postgres version is 7.2.

The table keeps the data above in a column named "NORMTEXT" and my SQL statement for the result above is:
select * from mytable where NORMTEXT >=  'LU'
Nothing out of the ordinary there..
Any help on this matter is highly appreciated!

Woops, a type above. My sql is of course:
select * from mytable where normtext>=  'LU' order by normtext;


Regards
Kristian
-- 
           \\|//
           (@ @)
+------ooO--(_)--Ooo----------+------------------------------+
| Kristian Jörg               | Phone: +46 54 153395         |
| Devo IT AB                  | Fax:   +46 54 153389         |
| Box 533, SE-651 12 KARLSTAD | mailto:kristian.jorg@devo.se |
| Sweden                      | http://www.devo.se           |
+-----------------------------+------------------------------+

-- 
           \\|//
           (@ @)
+------ooO--(_)--Ooo----------+------------------------------+
| Kristian Jörg               | Phone: +46 54 153395         |
| Devo IT AB                  | Fax:   +46 54 153389         |
| Box 533, SE-651 12 KARLSTAD | mailto:kristian.jorg@devo.se |
| Sweden                      | http://www.devo.se           |
+-----------------------------+------------------------------+
--------------090109010606080701080005--