pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSorting Problem
8+ messages / 7 participants
[nested] [flat]
* Sorting Problem
@ 1999-12-24 01:04 Mark Kirkwood <markir@hnz.co.nz>
0 siblings, 0 replies; 8+ messages in thread
From: Mark Kirkwood @ 1999-12-24 01:04 UTC (permalink / raw)
Dear List,
I gave been investigating how postgresql does sorting, using the query from my
last posting :
select d0.d0f1,
count(f.f1)
from dim0 d0,
fact0 f
where d0.d0key = f.d0key
and d0.d0f1 between '1996-05-01' and '1996-05-31'
group by d0.d0f1 ;
There is about 5-7M of data to sort - all with the same sort key ( d0f1 ).
I noticed that 19000K of temporary sort tables were written during query
execution, so I tried using backend options ( -o ) of : -S 25000.
Whilst there was no sort tables written with this option, the elapsed time for
the query was longer ! ( 35s instead of 14s )
The platform is redhat 6.0 and pgsql 6.5.3
There is 160M ram with about 60M free during the query exec
( note that hpux 11 with pgsql 6.5.3 gives similar results too...)
Is there something I need to set or unset, or is this expected ?
Cheers
Mark
^ permalink raw reply [nested|flat] 8+ messages in thread
* Sorting problem
@ 2003-10-15 03:30 George A.J <jinujosein@yahoo.com>
0 siblings, 2 replies; 8+ messages in thread
From: George A.J @ 2003-10-15 03:30 UTC (permalink / raw)
To: pgsql-sql
hi all,
i am using postgres 7.3.2 .i am converitng a mssql database to postgres.
now i am facing a strange problem. sorting based on a varchar field is not working
as expected. the non alphanumeric characters are not sorting based on the ascii
value of them.
i have the following table structure..
create table accounts
(
AcNo varchar (10),
Name varchar(100),
balance numeric(19,4)
)
when i used the query select * from accounts order by acno. the result is not correct
suppose that the acno field contains values '###1' ,'###2' ,'##10' , '#100'
the sort order in postgres is
'###1'
'##10'
'#100'
'###2'
But i want the result as follows
'###1'
'###2'
'##10'
'#100'
that means the ascii value of # should be considered for sorting..
what is the problem. is it the behaviour of postgres.
do i need to change any configuration. i am using all default configurations
or is it a bug...?
the problem actually is of < & > operators for varchar.
in a simple comparison
select '###2' < '##10'
returns false but i need true.
is there any solution exist. even if i replaced # with any non alphanumeric
character the result is same..
pls help
jinu jose
---------------------------------
Do you Yahoo!?
The New Yahoo! Shopping - with improved product search
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Sorting problem
@ 2003-10-15 03:39 Stephan Szabo <sszabo@megazone.bigpanda.com>
parent: George A.J <jinujosein@yahoo.com>
1 sibling, 0 replies; 8+ messages in thread
From: Stephan Szabo @ 2003-10-15 03:39 UTC (permalink / raw)
To: George A.J <jinujosein@yahoo.com>; +Cc: pgsql-sql
On Tue, 14 Oct 2003, George A.J wrote:
> hi all,
> i am using postgres 7.3.2 .i am converitng a mssql database to postgres.
> now i am facing a strange problem. sorting based on a varchar field is not working
> as expected. the non alphanumeric characters are not sorting based on the ascii
> value of them.
What OS and locale are you using? If it's not "C", you're probably
getting bit by the fact that many natural language collations (such as
en_US for example) don't consider most of the symbols except as tie
breakers when sorting. Unfortunately, to change the locale you need to
run initdb again with the appropriate locale, something like:
LANG="C" initdb -D /path/to/dataspace
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Sorting problem
@ 2003-10-15 15:42 Jean-Luc Lachance <jllachan@nsd.ca>
parent: George A.J <jinujosein@yahoo.com>
1 sibling, 1 reply; 8+ messages in thread
From: Jean-Luc Lachance @ 2003-10-15 15:42 UTC (permalink / raw)
To: George A.J <jinujosein@yahoo.com>; +Cc: pgsql-sql
You are obviously not using C locale.
If you can't change it for some reason, you can use:
select * from accounts order by int4( trim( acno, '#'));
JLL
"George A.J" wrote:
>
> hi all,
> i am using postgres 7.3.2 .i am converitng a mssql database to
> postgres.
> now i am facing a strange problem. sorting based on a varchar field is
> not working
> as expected. the non alphanumeric characters are not sorting based on
> the ascii
> value of them.
>
> i have the following table structure..
>
> create table accounts
> (
> AcNo varchar (10),
> Name varchar(100),
> balance numeric(19,4)
> )
>
> when i used the query select * from accounts order by acno. the
> result is not correct
>
> suppose that the acno field contains values '###1' ,'###2' ,'##10' ,
> '#100'
> the sort order in postgres is
> '###1'
> '##10'
> '#100'
> '###2'
> But i want the result as follows
> '###1'
> '###2'
> '##10'
> '#100'
>
> that means the ascii value of # should be considered for sorting..
> what is the problem. is it the behaviour of postgres.
> do i need to change any configuration. i am using all default
> configurations
> or is it a bug...?
> the problem actually is of < & > operators for varchar.
>
> in a simple comparison
>
> select '###2' < '##10'
>
> returns false but i need true.
>
> is there any solution exist. even if i replaced # with any non
> alphanumeric
> character the result is same..
>
> pls help
>
> jinu jose
>
> ----------------------------------------------------------------------
> Do you Yahoo!?
> The New Yahoo! Shopping - with improved product search
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Sorting problem
@ 2003-10-16 06:25 R. van Twisk <r.vantwisk@jongert.nl>
parent: Jean-Luc Lachance <jllachan@nsd.ca>
0 siblings, 0 replies; 8+ messages in thread
From: R. van Twisk @ 2003-10-16 06:25 UTC (permalink / raw)
To: pgsql-sql
I think what you actually want is natural sorting.
Ries
> -----Oorspronkelijk bericht-----
> Van: pgsql-sql-owner@postgresql.org
> [mailto:pgsql-sql-owner@postgresql.org]Namens Jean-Luc Lachance
> Verzonden: woensdag 15 oktober 2003 17:43
> Aan: George A.J
> CC: pgsql-sql@postgresql.org
> Onderwerp: Re: [SQL] Sorting problem
>
>
> You are obviously not using C locale.
> If you can't change it for some reason, you can use:
>
> select * from accounts order by int4( trim( acno, '#'));
>
> JLL
>
> "George A.J" wrote:
> >
> > hi all,
> > i am using postgres 7.3.2 .i am converitng a mssql database to
> > postgres.
> > now i am facing a strange problem. sorting based on a
> varchar field is
> > not working
> > as expected. the non alphanumeric characters are not
> sorting based on
> > the ascii
> > value of them.
> >
> > i have the following table structure..
> >
> > create table accounts
> > (
> > AcNo varchar (10),
> > Name varchar(100),
> > balance numeric(19,4)
> > )
> >
> > when i used the query select * from accounts order by acno. the
> > result is not correct
> >
> > suppose that the acno field contains values '###1' ,'###2' ,'##10' ,
> > '#100'
> > the sort order in postgres is
> > '###1'
> > '##10'
> > '#100'
> > '###2'
> > But i want the result as follows
> > '###1'
> > '###2'
> > '##10'
> > '#100'
> >
> > that means the ascii value of # should be considered for sorting..
> > what is the problem. is it the behaviour of postgres.
> > do i need to change any configuration. i am using all default
> > configurations
> > or is it a bug...?
> > the problem actually is of < & > operators for varchar.
> >
> > in a simple comparison
> >
> > select '###2' < '##10'
> >
> > returns false but i need true.
> >
> > is there any solution exist. even if i replaced # with any non
> > alphanumeric
> > character the result is same..
> >
> > pls help
> >
> > jinu jose
> >
> >
> ----------------------------------------------------------------------
> > Do you Yahoo!?
> > The New Yahoo! Shopping - with improved product search
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Sorting problem
@ 2004-07-12 08:27 Ruggero <ruggero.maria.rossi@libero.it>
0 siblings, 2 replies; 8+ messages in thread
From: ruggero.maria.rossi@libero.it @ 2004-07-12 08:27 UTC (permalink / raw)
To: pgsql-sql
Hi all,
I have a problem sorting varchar fields.
I will explain the problem with a simple example:
this query
select '##10' as sortfield
union
select '###1' as sortfield
order by sortfield
produces this correct output:
'###1'
'##10'
but this one
select '##10############--------####79909999' as sortfield
union
select '###1############--------####79909999' as sortfield
order by sortfield
produces this wrong output:
'##10############--------####79909999'
'###1############--------####79909999'
I'm using postgresql 7.4.3 on Suse Linux 9.1.
I created my db using: initdb --pgdata=../data/ --encoding=LATIN1
--lc-collate=it_IT --lc-ctype=it_IT
Any suggestion?
Tia
Ruggero
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Sorting problem
@ 2004-07-13 10:54 Stu <stu@santa-li.com>
parent: Ruggero <ruggero.maria.rossi@libero.it>
1 sibling, 0 replies; 8+ messages in thread
From: Stu @ 2004-07-13 10:54 UTC (permalink / raw)
To: pgsql-sql
Pop text in front of that first value and it works:
template1=# select text '##10############--------####79909999'
template1=# as "sortfield"
template1-# union
template1-# select '###1############--------####79909999'
template1-# order by sortfield;
sortfield
--------------------------------------
###1############--------####79909999
##10############--------####79909999
(2 rows)
Stu
Ruggero wrote:
> Hi all,
> I have a problem sorting varchar fields.
> I will explain the problem with a simple example:
>
> this query
> select '##10' as sortfield
> union
> select '###1' as sortfield
> order by sortfield
> produces this correct output:
> '###1'
> '##10'
>
> but this one
>
> select '##10############--------####79909999' as sortfield
> union
> select '###1############--------####79909999' as sortfield
> order by sortfield
> produces this wrong output:
> '##10############--------####79909999'
> '###1############--------####79909999'
>
> I'm using postgresql 7.4.3 on Suse Linux 9.1.
> I created my db using: initdb --pgdata=../data/ --encoding=LATIN1
> --lc-collate=it_IT --lc-ctype=it_IT
>
> Any suggestion?
> Tia
> Ruggero
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Sorting problem
@ 2004-07-16 19:14 Stephan Szabo <sszabo@megazone.bigpanda.com>
parent: Ruggero <ruggero.maria.rossi@libero.it>
1 sibling, 0 replies; 8+ messages in thread
From: Stephan Szabo @ 2004-07-16 19:14 UTC (permalink / raw)
To: Ruggero <ruggero.maria.rossi@libero.it>; +Cc: pgsql-sql
On Mon, 12 Jul 2004, Ruggero wrote:
> Hi all,
> I have a problem sorting varchar fields.
> I will explain the problem with a simple example:
>
> this query
> select '##10' as sortfield
> union
> select '###1' as sortfield
> order by sortfield
> produces this correct output:
> '###1'
> '##10'
>
> but this one
>
> select '##10############--------####79909999' as sortfield
> union
> select '###1############--------####79909999' as sortfield
> order by sortfield
> produces this wrong output:
> '##10############--------####79909999'
> '###1############--------####79909999'
>
> I'm using postgresql 7.4.3 on Suse Linux 9.1.
> I created my db using: initdb --pgdata=../data/ --encoding=LATIN1
> --lc-collate=it_IT --lc-ctype=it_IT
>
> Any suggestion?
it_IT looks like it's sorting based on the strings with the symbols
removed. I get similar behavior on my system using the unix sort command
with it_IT. This looks like it's a mismatch between what you want and the
collation you've provided.
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2004-07-16 19:14 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-12-24 01:04 Sorting Problem Mark Kirkwood <markir@hnz.co.nz>
2003-10-15 03:30 Sorting problem George A.J <jinujosein@yahoo.com>
2003-10-15 03:39 ` Re: Sorting problem Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-10-15 15:42 ` Re: Sorting problem Jean-Luc Lachance <jllachan@nsd.ca>
2003-10-16 06:25 ` Re: Sorting problem R. van Twisk <r.vantwisk@jongert.nl>
2004-07-12 08:27 Sorting problem Ruggero <ruggero.maria.rossi@libero.it>
2004-07-13 10:54 ` Re: Sorting problem Stu <stu@santa-li.com>
2004-07-16 19:14 ` Re: Sorting problem Stephan Szabo <sszabo@megazone.bigpanda.com>
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