pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSort order with spaces?
8+ messages / 3 participants
[nested] [flat]
* Sort order with spaces?
@ 2003-09-17 09:47 Kristian Jörg <krjg@devo.se>
2003-09-17 10:44 ` Re: Sort order with spaces? Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 8+ messages in thread
From: Kristian Jörg @ 2003-09-17 09:47 UTC (permalink / raw)
To: pgsql-sql
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
I seems that space is no considered at all so "M L R" is interpreted as
"MLR" i Postgres. This is a problem for me taht I need to resolve. How
do I do that?
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 |
+-----------------------------+------------------------------+
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Sort order with spaces?
2003-09-17 09:47 Sort order with spaces? Kristian Jörg <krjg@devo.se>
@ 2003-09-17 10:44 ` Richard Huxton <dev@archonet.com>
2003-09-17 12:30 ` Re: Sort order with spaces? Kristian Jörg <krjg@devo.se>
0 siblings, 1 reply; 8+ messages in thread
From: Richard Huxton @ 2003-09-17 10:44 UTC (permalink / raw)
To: Kristian Jörg <krjg@devo.se>; pgsql-sql
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.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Sort order with spaces?
2003-09-17 09:47 Sort order with spaces? Kristian Jörg <krjg@devo.se>
2003-09-17 10:44 ` Re: Sort order with spaces? Richard Huxton <dev@archonet.com>
@ 2003-09-17 12:30 ` Kristian Jörg <krjg@devo.se>
2003-09-17 12:39 ` Re: Sort order with spaces? Kristian Jörg <krjg@devo.se>
2003-09-17 14:06 ` Re: Sort order with spaces? Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 8+ messages in thread
From: Kristian Jörg @ 2003-09-17 12:30 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: pgsql-sql
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!
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 |
+-----------------------------+------------------------------+
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Sort order with spaces?
2003-09-17 09:47 Sort order with spaces? Kristian Jörg <krjg@devo.se>
2003-09-17 10:44 ` Re: Sort order with spaces? Richard Huxton <dev@archonet.com>
2003-09-17 12:30 ` Re: Sort order with spaces? Kristian Jörg <krjg@devo.se>
@ 2003-09-17 12:39 ` Kristian Jörg <krjg@devo.se>
2003-09-17 13:50 ` Re: Sort order with spaces? Richard Huxton <dev@archonet.com>
1 sibling, 1 reply; 8+ messages in thread
From: Kristian Jörg @ 2003-09-17 12:39 UTC (permalink / raw)
To: ; +Cc: Richard Huxton <dev@archonet.com>; pgsql-sql
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 |
+-----------------------------+------------------------------+
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Sort order with spaces?
2003-09-17 09:47 Sort order with spaces? Kristian Jörg <krjg@devo.se>
2003-09-17 10:44 ` Re: Sort order with spaces? Richard Huxton <dev@archonet.com>
2003-09-17 12:30 ` Re: Sort order with spaces? Kristian Jörg <krjg@devo.se>
2003-09-17 12:39 ` Re: Sort order with spaces? Kristian Jörg <krjg@devo.se>
@ 2003-09-17 13:50 ` Richard Huxton <dev@archonet.com>
0 siblings, 0 replies; 8+ messages in thread
From: Richard Huxton @ 2003-09-17 13:50 UTC (permalink / raw)
To: Kristian Jörg <krjg@devo.se>; +Cc: pgsql-sql
On Wednesday 17 September 2003 13:39, Kristian Jörg wrote:
> Kristian Jörg wrote:
> > Richard Huxton wrote:
> >>>
> >>>LUNDGREN
> >>>M L R
> >>>MACDOWELL
> >>>MUSCLE
> >>>
> >>>But in Postgres I get this order:
> >>>
> >>>LUNDGREN
> >>>MACDOWELL
> >>>M L R
> >>>MUSCLE
> >>
> Woops, a type above. My sql is of course:
> select * from mytable where normtext>= 'LU' order by normtext;
richtest=# select * from foo order by b;
a | b
---+-----------
1 | LUNDGREN
2 | M L R
3 | MACDOWELL
4 | MUSCLE
(4 rows)
LC_MESSAGES = 'C'
LC_MONETARY = 'C'
LC_NUMERIC = 'C'
LC_TIME = 'C'
Might be worth checking the release notes on the website to see if there's
anything mentioned.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Sort order with spaces?
2003-09-17 09:47 Sort order with spaces? Kristian Jörg <krjg@devo.se>
2003-09-17 10:44 ` Re: Sort order with spaces? Richard Huxton <dev@archonet.com>
2003-09-17 12:30 ` Re: Sort order with spaces? Kristian Jörg <krjg@devo.se>
@ 2003-09-17 14:06 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-09-17 14:26 ` Re: Sort order with spaces? Kristian Jörg <krjg@devo.se>
1 sibling, 1 reply; 8+ messages in thread
From: Tom Lane @ 2003-09-17 14:06 UTC (permalink / raw)
To: Kristian Jörg <krjg@devo.se>; +Cc: Richard Huxton <dev@archonet.com>; pgsql-sql
=?ISO-8859-1?Q?Kristian_J=F6rg?= <krjg@devo.se> writes:
> 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...
You didn't do it right then ...
7.2's initdb is not helpful about telling you exactly what locale
settings it's using, but you could use the contrib/pg_controldata
utility to check what LC_COLLATE and LC_CTYPE settings got used.
(If you don't want to build pg_controldata, "strings
$PGDATA/global/pg_control" will do as a rough-and-ready substitute.)
Theoretically it should work to do
export LC_COLLATE=C
export LC_CTYPE=C
initdb
but if you have LANG or other LC_xxx values in your environment,
it's possible that there is some conflict.
regards, tom lane
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Sort order with spaces?
2003-09-17 09:47 Sort order with spaces? Kristian Jörg <krjg@devo.se>
2003-09-17 10:44 ` Re: Sort order with spaces? Richard Huxton <dev@archonet.com>
2003-09-17 12:30 ` Re: Sort order with spaces? Kristian Jörg <krjg@devo.se>
2003-09-17 14:06 ` Re: Sort order with spaces? Tom Lane <tgl@sss.pgh.pa.us>
@ 2003-09-17 14:26 ` Kristian Jörg <krjg@devo.se>
2003-09-17 15:21 ` Re: Sort order with spaces? Kristian Jörg <krjg@devo.se>
0 siblings, 1 reply; 8+ messages in thread
From: Kristian Jörg @ 2003-09-17 14:26 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Richard Huxton <dev@archonet.com>; pgsql-sql
Tom Lane wrote:
>=?ISO-8859-1?Q?Kristian_J=F6rg?= <krjg@devo.se> writes:
>
>
>>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...
>>
>>
>
>You didn't do it right then ...
>
>7.2's initdb is not helpful about telling you exactly what locale
>settings it's using, but you could use the contrib/pg_controldata
>utility to check what LC_COLLATE and LC_CTYPE settings got used.
>(If you don't want to build pg_controldata, "strings
>$PGDATA/global/pg_control" will do as a rough-and-ready substitute.)
>
>
Unfortunately I am using a binary distribution from a RPM package, so I
don't have the source. Tried the strings stuff though and that worked. I
have sv_SE as locale for sure. So you are corect. I did not do initdb
correctly. Shame on me... :-)
>Theoretically it should work to do
> export LC_COLLATE=C
> export LC_CTYPE=C
> initdb
>but if you have LANG or other LC_xxx values in your environment,
>it's possible that there is some conflict.
>
> regards, tom lane
>
It is possible my locale settings did not get through to initdb since I
tried using the startup postgres command in /etc/init.d (with som
modifications of course). I will try again manually this time!
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 |
+-----------------------------+------------------------------+
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Sort order with spaces?
2003-09-17 09:47 Sort order with spaces? Kristian Jörg <krjg@devo.se>
2003-09-17 10:44 ` Re: Sort order with spaces? Richard Huxton <dev@archonet.com>
2003-09-17 12:30 ` Re: Sort order with spaces? Kristian Jörg <krjg@devo.se>
2003-09-17 14:06 ` Re: Sort order with spaces? Tom Lane <tgl@sss.pgh.pa.us>
2003-09-17 14:26 ` Re: Sort order with spaces? Kristian Jörg <krjg@devo.se>
@ 2003-09-17 15:21 ` Kristian Jörg <krjg@devo.se>
0 siblings, 0 replies; 8+ messages in thread
From: Kristian Jörg @ 2003-09-17 15:21 UTC (permalink / raw)
To: Kristian Jörg <krjg@devo.se>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Richard Huxton <dev@archonet.com>; pgsql-sql
Tom and Richard,
I have successfully fixed this problem now thanks to your support! The
setting of LC_COLLATE to C prior to running initdb fixed all problems
finally once I had sorted out my error in passing the environment
variables correctly.
Thanx!
/Kristian
Kristian Jörg wrote:
>
>
> Tom Lane wrote:
>
>>=?ISO-8859-1?Q?Kristian_J=F6rg?= <krjg@devo.se> writes:
>>
>>
>>>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...
>>>
>>>
>>
>>You didn't do it right then ...
>>
>>7.2's initdb is not helpful about telling you exactly what locale
>>settings it's using, but you could use the contrib/pg_controldata
>>utility to check what LC_COLLATE and LC_CTYPE settings got used.
>>(If you don't want to build pg_controldata, "strings
>>$PGDATA/global/pg_control" will do as a rough-and-ready substitute.)
>>
>>
> Unfortunately I am using a binary distribution from a RPM package, so
> I don't have the source. Tried the strings stuff though and that
> worked. I have sv_SE as locale for sure. So you are corect. I did not
> do initdb correctly. Shame on me... :-)
>
>>Theoretically it should work to do
>> export LC_COLLATE=C
>> export LC_CTYPE=C
>> initdb
>>but if you have LANG or other LC_xxx values in your environment,
>>it's possible that there is some conflict.
>>
>> regards, tom lane
>>
> It is possible my locale settings did not get through to initdb since
> I tried using the startup postgres command in /etc/init.d (with som
> modifications of course). I will try again manually this time!
>
> 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 |
+-----------------------------+------------------------------+
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2003-09-17 15:21 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-09-17 09:47 Sort order with spaces? Kristian Jörg <krjg@devo.se>
2003-09-17 10:44 ` Richard Huxton <dev@archonet.com>
2003-09-17 12:30 ` Kristian Jörg <krjg@devo.se>
2003-09-17 12:39 ` Kristian Jörg <krjg@devo.se>
2003-09-17 13:50 ` Richard Huxton <dev@archonet.com>
2003-09-17 14:06 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-09-17 14:26 ` Kristian Jörg <krjg@devo.se>
2003-09-17 15:21 ` Kristian Jörg <krjg@devo.se>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox