pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
order by
27+ messages / 20 participants
[nested] [flat]

* order by
@ 2000-02-29 18:56 Adrian Perez Camarena <adrianp@ife.org.mx>
  2000-03-01 09:18 ` Re: [GENERAL] order by Oleg Broytmann <phd@phd.russ.ru>
  0 siblings, 1 reply; 27+ messages in thread

From: Adrian Perez Camarena @ 2000-02-29 18:56 UTC (permalink / raw)
  To: pgsql-general

Hello friends
I have the next problem :
I need to order by name a table that contains name and other
fields of users.
I'm in Mexico city and the user's name looks like these:

Select name from prueba_insaculacion order by name;

name
--------------
BELTRAN
DIAZ
HAVEZ
LLUNAS
LOPEZ
NAÑES
NUÑEZ
PEREZ
PEÑA
YAÑES
ÁRSATE
ÑAURIBIA
ÚNAMUNO

as you can see the order by name is wrong,
mi question is the next: 

is there a file of postgres that I can change to tell postgres
that the language that I need to order by name correctly is
spanish? (or something like that)

thanks for your help...

Adrian Perez
IFE




^ permalink  raw  reply  [nested|flat] 27+ messages in thread

* Re: [GENERAL] order by
  2000-02-29 18:56 order by Adrian Perez Camarena <adrianp@ife.org.mx>
@ 2000-03-01 09:18 ` Oleg Broytmann <phd@phd.russ.ru>
  0 siblings, 0 replies; 27+ messages in thread

From: Oleg Broytmann @ 2000-03-01 09:18 UTC (permalink / raw)
  To: Adrian Perez Camarena <adrianp@ife.org.mx>; +Cc: pgsql-general

On Tue, 29 Feb 2000, Adrian Perez Camarena wrote:
> is there a file of postgres that I can change to tell postgres
> that the language that I need to order by name correctly is
> spanish? (or something like that)

   If I do understand your problem, the solution is here - you should
recompile Postgres with locale support, and run it with correct locale
settings.
   Look more docs on locale support in Postgres sourses and documentation.

Oleg.
---- 
    Oleg Broytmann     http://members.xoom.com/phd2/     phd2@earthling.net
           Programmers don't die, they just GOSUB without RETURN.




^ permalink  raw  reply  [nested|flat] 27+ messages in thread

* ORDER BY
@ 2006-11-14 22:03 MicroUser <a.shafar@gmail.com>
  2006-11-15 19:50 ` Re: ORDER BY Ashley Moran <work@ashleymoran.me.uk>
  2006-11-15 20:52 ` Re: ORDER BY Niklas Johansson <spot@tele2.se>
  2006-11-15 21:35 ` Re: ORDER BY Alexander Staubo <alex@purefiction.net>
  0 siblings, 3 replies; 27+ messages in thread

From: MicroUser @ 2006-11-14 22:03 UTC (permalink / raw)
  To: pgsql-general

Hi.

I have a table:

ID | Name
0 | Anna
1 | Other
2 | Link
3 | Fernando

I need sorted result but the way like this:

0 | Anna
3 | Fernando
2 | Link
1 | Other

Record '1 | Other' must by at the end of query result.

How I can get it?

Thx.




^ permalink  raw  reply  [nested|flat] 27+ messages in thread

* Re: ORDER BY
  2006-11-14 22:03 ORDER BY MicroUser <a.shafar@gmail.com>
@ 2006-11-15 19:50 ` Ashley Moran <work@ashleymoran.me.uk>
  2 siblings, 0 replies; 27+ messages in thread

From: Ashley Moran @ 2006-11-15 19:50 UTC (permalink / raw)
  To: MicroUser <a.shafar@gmail.com>; +Cc: pgsql-general


On Nov 14, 2006, at 10:03 pm, MicroUser wrote:

> Hi.
>
> I have a table:
>
> ID | Name
> 0 | Anna
> 1 | Other
> 2 | Link
> 3 | Fernando
>
> I need sorted result but the way like this:
>
> 0 | Anna
> 3 | Fernando
> 2 | Link
> 1 | Other
>
> Record '1 | Other' must by at the end of query result.
>
> How I can get it?
>
> Thx.
>


I suppose a nasty way would be with something like
   ORDER BY CASE "Name" WHEN 'Other' THEN 'zzzzzzzzz' ELSE "Name" END

But this might work well enough if you've only got a few rows in the  
table

Ashley



^ permalink  raw  reply  [nested|flat] 27+ messages in thread

* Re: ORDER BY
  2006-11-14 22:03 ORDER BY MicroUser <a.shafar@gmail.com>
@ 2006-11-15 20:52 ` Niklas Johansson <spot@tele2.se>
  2006-11-15 22:28   ` Re: ORDER BY Brent Wood <b.wood@niwa.co.nz>
  2 siblings, 1 reply; 27+ messages in thread

From: Niklas Johansson @ 2006-11-15 20:52 UTC (permalink / raw)
  To: MicroUser <a.shafar@gmail.com>; +Cc: pgsql-general


On 14 nov 2006, at 23.03, MicroUser wrote:
> I need sorted result but the way like this:
>
> 0 | Anna
> 3 | Fernando
> 2 | Link
> 1 | Other
>
> Record '1 | Other' must by at the end of query result.
>
> How I can get it?


Well, maybe not the answer you're looking for, but a rather clean way  
to do this would be to only store actual names in the table, let your  
application do the select and sort, and then add the 'Other' at runtime.

If the 'Other' needs to be stored, perhaps it could be represented  
with a NULL value instead? (It's not really a name, just a  
placeholder for not knowing, isn't it?)


Sincerely,

Niklas Johansson
Phone: +46-322-108 18
Mobile: +46-708-55 86 90






^ permalink  raw  reply  [nested|flat] 27+ messages in thread

* Re: ORDER BY
  2006-11-14 22:03 ORDER BY MicroUser <a.shafar@gmail.com>
  2006-11-15 20:52 ` Re: ORDER BY Niklas Johansson <spot@tele2.se>
@ 2006-11-15 22:28   ` Brent Wood <b.wood@niwa.co.nz>
  0 siblings, 0 replies; 27+ messages in thread

From: Brent Wood @ 2006-11-15 22:28 UTC (permalink / raw)
  To: ; +Cc: MicroUser <a.shafar@gmail.com>; pgsql-general


> On 14 nov 2006, at 23.03, MicroUser wrote:
>> I need sorted result but the way like this:
>>
>> 0 | Anna
>> 3 | Fernando
>> 2 | Link
>> 1 | Other
>>
>> Record '1 | Other' must by at the end of query result.
>>
>> How I can get it?

Something along these lines might work.

select * from table
where <field> !=1
order desc by <field>

union

select * from table
where <field>=1;

or if the last record is always the same, hard code the values in the 
sql instead of querying the table, which will save a few milliseconds :-)

If it is the "other" and not the "1" that you want last, change the 
first query where clause to where <field> != 'Other' & similarly change 
the second one.


Cheers,

  Brent Wood



^ permalink  raw  reply  [nested|flat] 27+ messages in thread

* Re: ORDER BY
  2006-11-14 22:03 ORDER BY MicroUser <a.shafar@gmail.com>
@ 2006-11-15 21:35 ` Alexander Staubo <alex@purefiction.net>
  2006-11-15 22:59   ` Re: ORDER BY George Pavlov <gpavlov@mynewplace.com>
  2006-11-16 10:04   ` Re: ORDER BY Harald Fuchs <hf1110x@protecting.net>
  2 siblings, 2 replies; 27+ messages in thread

From: Alexander Staubo @ 2006-11-15 21:35 UTC (permalink / raw)
  To: MicroUser <a.shafar@gmail.com>; +Cc: pgsql-general

On Nov 14, 2006, at 23:03 , MicroUser wrote:

> I need sorted result but the way like this:
>
> 0 | Anna
> 3 | Fernando
> 2 | Link
> 1 | Other
>
> Record '1 | Other' must by at the end of query result.

It's not apparent from your example that you want something other  
than a purely lexicographic sort order (after all, "Other" comes  
after "Link", "Fernando" and "Anna", so "order by name" already gets  
you what you want), but I assume that's what you mean.

If your table is sufficiently small, and the complexity of the actual  
query sufficiently low, prepending an expression sort key might suffice:

   select * from foo
   order by (case name when 'Other' then 1 else 0 end), name

Note that PostgreSQL is slow at evaluating case expressions, and this  
might prove too slow. For larger tables, you may have to resort to a  
union:

   select * from foo where name != 'Other' order by name
   union
   select * from foo where name = 'Other'

Alexander.



^ permalink  raw  reply  [nested|flat] 27+ messages in thread

* Re: ORDER BY
  2006-11-14 22:03 ORDER BY MicroUser <a.shafar@gmail.com>
  2006-11-15 21:35 ` Re: ORDER BY Alexander Staubo <alex@purefiction.net>
@ 2006-11-15 22:59   ` George Pavlov <gpavlov@mynewplace.com>
  2006-11-15 23:53     ` Re: ORDER BY Chris Mulcahy <pgsql@cmulcahy.com>
  2006-11-16 06:43     ` Re: ORDER BY John Sidney-Woollett <johnsw@wardbrook.com>
  2006-11-16 08:58     ` Re: ORDER BY Alban Hertroys <alban@magproductions.nl>
  1 sibling, 3 replies; 27+ messages in thread

From: George Pavlov @ 2006-11-15 22:59 UTC (permalink / raw)
  To: Alexander Staubo <alex@purefiction.net>; MicroUser <a.shafar@gmail.com>; +Cc: pgsql-general

> For larger tables, you may have to resort to a  
> union:
> 
>    select * from foo where name != 'Other' order by name
>    union
>    select * from foo where name = 'Other'

Alas, this suggestion is wrong on two counts: (a) UNION expects a single
ORDER BY that applies to the whole recordset and which has to come at
the end; (b) UNION re-sorts anyway (it needs to eliminate the dupes) --
maybe you are thinking UNION ALL? So, to follow your advice he may want
a query like this, although it seems quite silly and there still isn't
an ironclad guarantee re. the final result sorting:

select * from 
  (select * from foo where name != 'Other' order by name) x
union all
select * from foo where name = 'Other'



^ permalink  raw  reply  [nested|flat] 27+ messages in thread

* Re: ORDER BY
  2006-11-14 22:03 ORDER BY MicroUser <a.shafar@gmail.com>
  2006-11-15 21:35 ` Re: ORDER BY Alexander Staubo <alex@purefiction.net>
  2006-11-15 22:59   ` Re: ORDER BY George Pavlov <gpavlov@mynewplace.com>
@ 2006-11-15 23:53     ` Chris Mulcahy <pgsql@cmulcahy.com>
  2006-11-21 09:13       ` Re: ORDER BY Paefgen, Peter (LDS) <Peter.Paefgen@lds.nrw.de>
  2 siblings, 1 reply; 27+ messages in thread

From: Chris Mulcahy @ 2006-11-15 23:53 UTC (permalink / raw)
  To: George Pavlov <gpavlov@mynewplace.com>; +Cc: Alexander Staubo <alex@purefiction.net>; MicroUser <a.shafar@gmail.com>; pgsql-general

On Wed, 2006-11-15 at 14:59 -0800, George Pavlov wrote:
Alas, this suggestion is wrong on two counts: (a) UNION expects a single
> ORDER BY that applies to the whole recordset and which has to come at
> the end; (b) UNION re-sorts anyway (it needs to eliminate the dupes)
--
> maybe you are thinking UNION ALL? So, to follow your advice he may
want
> a query like this, although it seems quite silly and there still isn't
> an ironclad guarantee re. the final result sorting:
> 
> select * from 
>   (select * from foo where name != 'Other' order by name) x
> union all
> select * from foo where name = 'Other'
> 
>

Here ya go.
 
select 1 SortCol, * from foo where name != 'Other'
UNION ALL
select 2 SortCol, * from foo where name = 'Other'
order by SortCol;





^ permalink  raw  reply  [nested|flat] 27+ messages in thread

* Re: ORDER BY
  2006-11-14 22:03 ORDER BY MicroUser <a.shafar@gmail.com>
  2006-11-15 21:35 ` Re: ORDER BY Alexander Staubo <alex@purefiction.net>
  2006-11-15 22:59   ` Re: ORDER BY George Pavlov <gpavlov@mynewplace.com>
  2006-11-15 23:53     ` Re: ORDER BY Chris Mulcahy <pgsql@cmulcahy.com>
@ 2006-11-21 09:13       ` Paefgen, Peter (LDS) <Peter.Paefgen@lds.nrw.de>
  2006-11-21 15:45         ` Re: ORDER BY Shane Ambler <pgsql@007Marketing.com>
  0 siblings, 1 reply; 27+ messages in thread

From: Paefgen, Peter (LDS) @ 2006-11-21 09:13 UTC (permalink / raw)
  To: Chris Mulcahy <pgsql@cmulcahy.com>; +Cc: pgsql-general

Hello,

a) it is right, to place an ORDER BY at the end of the statement. That is the
correct place to do this.

but 

b) UNION doesn't mean anyway an sort. UNION mean "eliminate dups". But there are
several techniques to do that. One of them is "sort". So, if the development
crew chooses to change the elimination algorithme, that would be, in sight of an
UNION, ok .

Best wishes,
Regards,
Peter





---------------------------------------------------------------------------
Peter Paefgen
Landesamt für Datenverarbeitung und Statistik NRW.
Telefon: 0211 9449 2390
Fax: 0211 9449 8390
Mail: peter.paefgen@lds.nrw.de

-----Ursprüngliche Nachricht-----
Von: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] Im Auftrag von Chris Mulcahy
Gesendet: Donnerstag, 16. November 2006 00:53
An: George Pavlov
Cc: Alexander Staubo; MicroUser; pgsql-general@postgresql.org
Betreff: Re: [GENERAL] ORDER BY

On Wed, 2006-11-15 at 14:59 -0800, George Pavlov wrote:
Alas, this suggestion is wrong on two counts: (a) UNION expects a single
> ORDER BY that applies to the whole recordset and which has to come at 
> the end; (b) UNION re-sorts anyway (it needs to eliminate the dupes)
--
> maybe you are thinking UNION ALL? So, to follow your advice he may
want
> a query like this, although it seems quite silly and there still isn't 
> an ironclad guarantee re. the final result sorting:
> 
> select * from 
>   (select * from foo where name != 'Other' order by name) x union all 
> select * from foo where name = 'Other'
> 
>

Here ya go.
 
select 1 SortCol, * from foo where name != 'Other'
UNION ALL
select 2 SortCol, * from foo where name = 'Other'
order by SortCol;



---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match



^ permalink  raw  reply  [nested|flat] 27+ messages in thread

* Re: ORDER BY
  2006-11-14 22:03 ORDER BY MicroUser <a.shafar@gmail.com>
  2006-11-15 21:35 ` Re: ORDER BY Alexander Staubo <alex@purefiction.net>
  2006-11-15 22:59   ` Re: ORDER BY George Pavlov <gpavlov@mynewplace.com>
  2006-11-15 23:53     ` Re: ORDER BY Chris Mulcahy <pgsql@cmulcahy.com>
  2006-11-21 09:13       ` Re: ORDER BY Paefgen, Peter (LDS) <Peter.Paefgen@lds.nrw.de>
@ 2006-11-21 15:45         ` Shane Ambler <pgsql@007Marketing.com>
  0 siblings, 0 replies; 27+ messages in thread

From: Shane Ambler @ 2006-11-21 15:45 UTC (permalink / raw)
  To: Paefgen, Peter (LDS) <Peter.Paefgen@lds.nrw.de>; +Cc: Chris Mulcahy <pgsql@cmulcahy.com>; pgsql-general

Paefgen, Peter (LDS) wrote:
> Hello,
> 
> a) it is right, to place an ORDER BY at the end of the statement. That is the
> correct place to do this.

Correct - the ORDER BY at the end of the statement will apply to the 
result set after the UNION has been done.
ORDER BY can be included in the sub-expression SELECT statements only if 
they are enclosed in parenthesis and I believe it *must* also be 
accompanied by a LIMIT clause.

> but 
> 
> b) UNION doesn't mean anyway an sort. UNION mean "eliminate dups". But there are

UNION means to join/combine together and if used on it's own will 
eliminate duplicate rows in the result set.

Using UNION ALL will include any duplicates and run somewhat faster.

UNION itself does not imply any sort order but ORDER BY can be applied 
to the result set of the UNION.

> several techniques to do that. One of them is "sort". So, if the development
> crew chooses to change the elimination algorithme, that would be, in sight of an
> UNION, ok .

As long as the columns returned match up to satisfy the UNION criteria 
then changing the elimination algorithm will not break your use of UNION.

If they want to use ORDER BY x LIMIT 2 then that sub-select will need to 
be surrounded by parenthesis.

> Best wishes,
> Regards,
> Peter
> 

-- 

Shane Ambler
pgSQL@007Marketing.com

Get Sheeky @ http://Sheeky.Biz



^ permalink  raw  reply  [nested|flat] 27+ messages in thread

* Re: ORDER BY
  2006-11-14 22:03 ORDER BY MicroUser <a.shafar@gmail.com>
  2006-11-15 21:35 ` Re: ORDER BY Alexander Staubo <alex@purefiction.net>
  2006-11-15 22:59   ` Re: ORDER BY George Pavlov <gpavlov@mynewplace.com>
@ 2006-11-16 06:43     ` John Sidney-Woollett <johnsw@wardbrook.com>
  2 siblings, 0 replies; 27+ messages in thread

From: John Sidney-Woollett @ 2006-11-16 06:43 UTC (permalink / raw)
  To: George Pavlov <gpavlov@mynewplace.com>; +Cc: Alexander Staubo <alex@purefiction.net>; MicroUser <a.shafar@gmail.com>; pgsql-general

Alternative options for what they're worth - you'd have to explain to 
see how efficient they are

select id, name from (
   select lower(name) as sortkey, id, name from table where name != 'Other'
   union
   select 'zzzzz' as sortkey, id, name from table where name = 'Other'
) as t
order by sortkey


select id, name from (
   select case when name='Other' then 'zzzzz' else lower(name) end as 
sortkey, id, name from table

) as t
order by sortkey

Notice that the sort will be case insensitive in these examples which 
may be something that you also want.

John

George Pavlov wrote:
>> For larger tables, you may have to resort to a  
>> union:
>>
>>    select * from foo where name != 'Other' order by name
>>    union
>>    select * from foo where name = 'Other'
> 
> Alas, this suggestion is wrong on two counts: (a) UNION expects a single
> ORDER BY that applies to the whole recordset and which has to come at
> the end; (b) UNION re-sorts anyway (it needs to eliminate the dupes) --
> maybe you are thinking UNION ALL? So, to follow your advice he may want
> a query like this, although it seems quite silly and there still isn't
> an ironclad guarantee re. the final result sorting:
> 
> select * from 
>   (select * from foo where name != 'Other' order by name) x
> union all
> select * from foo where name = 'Other'
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>        choose an index scan if your joining column's datatypes do not
>        match



^ permalink  raw  reply  [nested|flat] 27+ messages in thread

* Re: ORDER BY
  2006-11-14 22:03 ORDER BY MicroUser <a.shafar@gmail.com>
  2006-11-15 21:35 ` Re: ORDER BY Alexander Staubo <alex@purefiction.net>
  2006-11-15 22:59   ` Re: ORDER BY George Pavlov <gpavlov@mynewplace.com>
@ 2006-11-16 08:58     ` Alban Hertroys <alban@magproductions.nl>
  2 siblings, 0 replies; 27+ messages in thread

From: Alban Hertroys @ 2006-11-16 08:58 UTC (permalink / raw)
  To: George Pavlov <gpavlov@mynewplace.com>; +Cc: Alexander Staubo <alex@purefiction.net>; MicroUser <a.shafar@gmail.com>; pgsql-general

George Pavlov wrote:
>> For larger tables, you may have to resort to a  
>> union:
>>
>>    select * from foo where name != 'Other' order by name
>>    union
>>    select * from foo where name = 'Other'
> 
> Alas, this suggestion is wrong on two counts: (a) UNION expects a single
> ORDER BY that applies to the whole recordset and which has to come at
> the end; (b) UNION re-sorts anyway (it needs to eliminate the dupes) --

You can also add an imaginary key to sort on, like

select 1, * from foo where name != 'Other'
union all
select 9, * from foo where name = 'Other'
order by 1;

Or independent of column order:

select 1 AS sort_key, * from foo where name != 'Other'
union all
select 9 AS sort_key, * from foo where name = 'Other'
order by sort_key;

Regards,
-- 
Alban Hertroys
alban@magproductions.nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
   7500 AK Enschede

// Integrate Your World //



^ permalink  raw  reply  [nested|flat] 27+ messages in thread

* Re: ORDER BY
  2006-11-14 22:03 ORDER BY MicroUser <a.shafar@gmail.com>
  2006-11-15 21:35 ` Re: ORDER BY Alexander Staubo <alex@purefiction.net>
@ 2006-11-16 10:04   ` Harald Fuchs <hf1110x@protecting.net>
  2006-11-21 03:05     ` Re: ORDER BY Gurjeet Singh <singh.gurjeet@gmail.com>
  1 sibling, 1 reply; 27+ messages in thread

From: Harald Fuchs @ 2006-11-16 10:04 UTC (permalink / raw)
  To: pgsql-general

In article <DFDFB8FE-1787-4508-97C4-5FD12357936A@purefiction.net>,
Alexander Staubo <alex@purefiction.net> writes:

> On Nov 14, 2006, at 23:03 , MicroUser wrote:
>> I need sorted result but the way like this:
>> 
>> 0 | Anna
>> 3 | Fernando
>> 2 | Link
>> 1 | Other
>> 
>> Record '1 | Other' must by at the end of query result.

> It's not apparent from your example that you want something other
> than a purely lexicographic sort order (after all, "Other" comes
> after "Link", "Fernando" and "Anna", so "order by name" already gets
> you what you want), but I assume that's what you mean.

> If your table is sufficiently small, and the complexity of the actual
> query sufficiently low, prepending an expression sort key might
> suffice:

>    select * from foo
>    order by (case name when 'Other' then 1 else 0 end), name

Why so complicated?

SELECT whatever FROM foo ORDER BY name = 'Other', name




^ permalink  raw  reply  [nested|flat] 27+ messages in thread

* Re: ORDER BY
  2006-11-14 22:03 ORDER BY MicroUser <a.shafar@gmail.com>
  2006-11-15 21:35 ` Re: ORDER BY Alexander Staubo <alex@purefiction.net>
  2006-11-16 10:04   ` Re: ORDER BY Harald Fuchs <hf1110x@protecting.net>
@ 2006-11-21 03:05     ` Gurjeet Singh <singh.gurjeet@gmail.com>
  0 siblings, 0 replies; 27+ messages in thread

From: Gurjeet Singh @ 2006-11-21 03:05 UTC (permalink / raw)
  To: Harald Fuchs <hf1110x@protecting.net>; +Cc: pgsql-general

On 16 Nov 2006 11:04:15 +0100, Harald Fuchs <hf1110x@protecting.net> wrote:
>
> Why so complicated?
>
> SELECT whatever FROM foo ORDER BY name = 'Other', name
>

This is by far the simplest (and intelligent) of them all. And it will use a
single scan of the underlying table (or index, if used), so should prove to
be faster than the other UNION schemes.

Another tweak: If possible, use the integer column (ID) instead of the Name
column.

Regards,

-- 
gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | yahoo }.com

^ permalink  raw  reply  [nested|flat] 27+ messages in thread

* order by
@ 2023-05-11 14:29 Marc Millas <marc.millas@mokadb.com>
  2023-05-11 14:43 ` Re: order by Adrian Klaver <adrian.klaver@aklaver.com>
  2023-05-11 14:48 ` Re: order by Adam Scott <adam.c.scott@gmail.com>
  2023-05-11 21:12 ` Re: order by Ron <ronljohnsonjr@gmail.com>
  0 siblings, 3 replies; 27+ messages in thread

From: Marc Millas @ 2023-05-11 14:29 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>

Hi,

I keep on investigating on the "death postgres" subject
but open a new thread as I don't know if it's related to my pb.

I have 2 different clusters, on 2 different machines, one is prod, the
second test.
Same data volumes.

On prod if I do
select col_a, count(col_a) from table_a group by col_a order by col_a desc,
I get the numbers of NULL on top.
To get the number of NULL on top on the test db, I have to
select col_a, count(col_a) from table_a group by col_a order by col_a asc.

so, it looks like there is something different within the b-tree operator
class of varchar (?!?)
between those 2 clusters.

What can I check to to explain this difference as, to my understanding,
it's not a postgresql.conf parameter.

thanks

Marc MILLAS
Senior Architect
+33607850334
www.mokadb.com

^ permalink  raw  reply  [nested|flat] 27+ messages in thread

* Re: order by
  2023-05-11 14:29 order by Marc Millas <marc.millas@mokadb.com>
@ 2023-05-11 14:43 ` Adrian Klaver <adrian.klaver@aklaver.com>
  2023-05-11 15:00   ` Re: order by Marc Millas <marc.millas@mokadb.com>
  2 siblings, 1 reply; 27+ messages in thread

From: Adrian Klaver @ 2023-05-11 14:43 UTC (permalink / raw)
  To: Marc Millas <marc.millas@mokadb.com>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>

On 5/11/23 07:29, Marc Millas wrote:
> Hi,
> 
> I keep on investigating on the "death postgres" subject
> but open a new thread as I don't know if it's related to my pb.
> 
> I have 2 different clusters, on 2 different machines, one is prod, the 
> second test.
> Same data volumes.

How can they be sharing the same data 'volume'?

Do you mean you are doing dump/restore between them?

Postgres version for each cluster is?

> 
> On prod if I do
> select col_a, count(col_a) from table_a group by col_a order by col_a desc,
> I get the numbers of NULL on top.
> To get the number of NULL on top on the test db, I have to
> select col_a, count(col_a) from table_a group by col_a order by col_a asc.
> 
> so, it looks like there is something different within the b-tree 
> operator class of varchar (?!?)
> between those 2 clusters.
> 
> What can I check to to explain this difference as, to my understanding, 
> it's not a postgresql.conf parameter.
> 
> thanks
> 
> Marc MILLAS
> Senior Architect
> +33607850334
> www.mokadb.com <http://www.mokadb.com;
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com






^ permalink  raw  reply  [nested|flat] 27+ messages in thread

* Re: order by
  2023-05-11 14:29 order by Marc Millas <marc.millas@mokadb.com>
  2023-05-11 14:43 ` Re: order by Adrian Klaver <adrian.klaver@aklaver.com>
@ 2023-05-11 15:00   ` Marc Millas <marc.millas@mokadb.com>
  2023-05-11 15:23     ` Re: order by Adrian Klaver <adrian.klaver@aklaver.com>
  0 siblings, 1 reply; 27+ messages in thread

From: Marc Millas @ 2023-05-11 15:00 UTC (permalink / raw)
  To: Adrian Klaver <adrian.klaver@aklaver.com>; +Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>

On Thu, May 11, 2023 at 4:43 PM Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

> On 5/11/23 07:29, Marc Millas wrote:
> > Hi,
> >
> > I keep on investigating on the "death postgres" subject
> > but open a new thread as I don't know if it's related to my pb.
> >
> > I have 2 different clusters, on 2 different machines, one is prod, the
> > second test.
> > Same data volumes.
>
> How can they be sharing the same data 'volume'?
>
    roughly: one table is 13080000 lines and the second is 13100000 lines,
the data comes from yet another DB.

> those 2 tables have no indexes. they are used to build kind of aggregates
> thru multiple left joins.
>


> Do you mean you are doing dump/restore between them?
>
no

>
> Postgres version for each cluster is?
> 14.2
>



> >
> > On prod if I do
> > select col_a, count(col_a) from table_a group by col_a order by col_a
> desc,
> > I get the numbers of NULL on top.
> > To get the number of NULL on top on the test db, I have to
> > select col_a, count(col_a) from table_a group by col_a order by col_a
> asc.
> >
> > so, it looks like there is something different within the b-tree
> > operator class of varchar (?!?)
> > between those 2 clusters.
> >
> > What can I check to to explain this difference as, to my understanding,
> > it's not a postgresql.conf parameter.
> >
> > thanks
> >
> > Marc MILLAS
> > Senior Architect
> > +33607850334
> > www.mokadb.com <http://www.mokadb.com;
> >
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com
>
>

^ permalink  raw  reply  [nested|flat] 27+ messages in thread

* Re: order by
  2023-05-11 14:29 order by Marc Millas <marc.millas@mokadb.com>
  2023-05-11 14:43 ` Re: order by Adrian Klaver <adrian.klaver@aklaver.com>
  2023-05-11 15:00   ` Re: order by Marc Millas <marc.millas@mokadb.com>
@ 2023-05-11 15:23     ` Adrian Klaver <adrian.klaver@aklaver.com>
  2023-05-11 15:29       ` Re: order by Marc Millas <marc.millas@mokadb.com>
  0 siblings, 1 reply; 27+ messages in thread

From: Adrian Klaver @ 2023-05-11 15:23 UTC (permalink / raw)
  To: Marc Millas <marc.millas@mokadb.com>; +Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>

On 5/11/23 08:00, Marc Millas wrote:
> 
> On Thu, May 11, 2023 at 4:43 PM Adrian Klaver <adrian.klaver@aklaver.com 
> <mailto:adrian.klaver@aklaver.com>> wrote:
> 
>     On 5/11/23 07:29, Marc Millas wrote:
>      > Hi,
>      >
>      > I keep on investigating on the "death postgres" subject
>      > but open a new thread as I don't know if it's related to my pb.
>      >
>      > I have 2 different clusters, on 2 different machines, one is
>     prod, the
>      > second test.
>      > Same data volumes.
> 
>     How can they be sharing the same data 'volume'?
> 
>      roughly: one table is 13080000 lines and the second is 13100000 
> lines, the data comes from yet another DB.
> 
>     those 2 tables have no indexes. they are used to build kind of
>     aggregates thru multiple left joins.
> 
>     Do you mean you are doing dump/restore between them?
> 
> no

So how is the data getting from the third database to the prod and test 
clusters?

For the machines hosting the third db, the prod and test clusters what are?:

OS

OS version

locale


> 
> 
>     Postgres version for each cluster is?
>     14.2

FYI, 14.8 has just been released so the clusters are behind by 6 bug fix 
releases.



-- 
Adrian Klaver
adrian.klaver@aklaver.com






^ permalink  raw  reply  [nested|flat] 27+ messages in thread

* Re: order by
  2023-05-11 14:29 order by Marc Millas <marc.millas@mokadb.com>
  2023-05-11 14:43 ` Re: order by Adrian Klaver <adrian.klaver@aklaver.com>
  2023-05-11 15:00   ` Re: order by Marc Millas <marc.millas@mokadb.com>
  2023-05-11 15:23     ` Re: order by Adrian Klaver <adrian.klaver@aklaver.com>
@ 2023-05-11 15:29       ` Marc Millas <marc.millas@mokadb.com>
  2023-05-11 15:37         ` Re: order by Adrian Klaver <adrian.klaver@aklaver.com>
  2023-05-11 22:42         ` Re: order by Kirk Wolak <wolakk@gmail.com>
  0 siblings, 2 replies; 27+ messages in thread

From: Marc Millas @ 2023-05-11 15:29 UTC (permalink / raw)
  To: Adrian Klaver <adrian.klaver@aklaver.com>; +Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>

On Thu, May 11, 2023 at 5:23 PM Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

> On 5/11/23 08:00, Marc Millas wrote:
> >
> > On Thu, May 11, 2023 at 4:43 PM Adrian Klaver <adrian.klaver@aklaver.com
> > <mailto:adrian.klaver@aklaver.com>> wrote:
> >
> >     On 5/11/23 07:29, Marc Millas wrote:
> >      > Hi,
> >      >
> >      > I keep on investigating on the "death postgres" subject
> >      > but open a new thread as I don't know if it's related to my pb.
> >      >
> >      > I have 2 different clusters, on 2 different machines, one is
> >     prod, the
> >      > second test.
> >      > Same data volumes.
> >
> >     How can they be sharing the same data 'volume'?
> >
> >      roughly: one table is 13080000 lines and the second is 13100000
> > lines, the data comes from yet another DB.
> >
> >     those 2 tables have no indexes. they are used to build kind of
> >     aggregates thru multiple left joins.
> >
> >     Do you mean you are doing dump/restore between them?
> >
> > no
>
> So how is the data getting from the third database to the prod and test
> clusters?
>
> For the machines hosting the third db, the prod and test clusters what
> are?:
>

should I understand that you suggest that the way the data is inserted Do
change the behaviour of the ORDER BY clause ??

>
> OS
>
> OS version
>
> locale
>
>
> >
> >
> >     Postgres version for each cluster is?
> >     14.2
>
> FYI, 14.8 has just been released so the clusters are behind by 6 bug fix
> releases.
> Sadly.. I know.
>
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com
>
>

^ permalink  raw  reply  [nested|flat] 27+ messages in thread

* Re: order by
  2023-05-11 14:29 order by Marc Millas <marc.millas@mokadb.com>
  2023-05-11 14:43 ` Re: order by Adrian Klaver <adrian.klaver@aklaver.com>
  2023-05-11 15:00   ` Re: order by Marc Millas <marc.millas@mokadb.com>
  2023-05-11 15:23     ` Re: order by Adrian Klaver <adrian.klaver@aklaver.com>
  2023-05-11 15:29       ` Re: order by Marc Millas <marc.millas@mokadb.com>
@ 2023-05-11 15:37         ` Adrian Klaver <adrian.klaver@aklaver.com>
  1 sibling, 0 replies; 27+ messages in thread

From: Adrian Klaver @ 2023-05-11 15:37 UTC (permalink / raw)
  To: Marc Millas <marc.millas@mokadb.com>; +Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>

On 5/11/23 08:29, Marc Millas wrote:
> 
> 

>     So how is the data getting from the third database to the prod and test
>     clusters?
> 
>     For the machines hosting the third db, the prod and test clusters
>     what are?:
> 
> should I understand that you suggest that the way the data is inserted 
> Do change the behaviour of the ORDER BY clause ??

What I am saying is we need context. You are there and know what you are 
looking at and how it got there, we don't. At this point I don't know 
anything as I don't know the data operations involved.

So how did the data get from the third database to the others?

Context is also why the information to the below was requested.

> 
> 
>     OS
> 
>     OS version
> 
>     locale
> 

Without solid information anything said is based on a good deal of 
assuming and we know where that leads.

> 
>      >
>      >
>      >     Postgres version for each cluster is?
>      >     14.2
> 
>     FYI, 14.8 has just been released so the clusters are behind by 6 bug
>     fix
>     releases.
>     Sadly.. I know.
> 
> 
>     -- 
>     Adrian Klaver
>     adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com






^ permalink  raw  reply  [nested|flat] 27+ messages in thread

* Re: order by
  2023-05-11 14:29 order by Marc Millas <marc.millas@mokadb.com>
  2023-05-11 14:43 ` Re: order by Adrian Klaver <adrian.klaver@aklaver.com>
  2023-05-11 15:00   ` Re: order by Marc Millas <marc.millas@mokadb.com>
  2023-05-11 15:23     ` Re: order by Adrian Klaver <adrian.klaver@aklaver.com>
  2023-05-11 15:29       ` Re: order by Marc Millas <marc.millas@mokadb.com>
@ 2023-05-11 22:42         ` Kirk Wolak <wolakk@gmail.com>
  1 sibling, 0 replies; 27+ messages in thread

From: Kirk Wolak @ 2023-05-11 22:42 UTC (permalink / raw)
  To: Marc Millas <marc.millas@mokadb.com>; +Cc: Adrian Klaver <adrian.klaver@aklaver.com>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>

On Thu, May 11, 2023 at 11:30 AM Marc Millas <marc.millas@mokadb.com> wrote:

> On Thu, May 11, 2023 at 5:23 PM Adrian Klaver <adrian.klaver@aklaver.com>
> wrote:
>
>> On 5/11/23 08:00, Marc Millas wrote:
>> >
>> > On Thu, May 11, 2023 at 4:43 PM Adrian Klaver <
>> adrian.klaver@aklaver.com
>> > <mailto:adrian.klaver@aklaver.com>> wrote:
>> >
>> >     On 5/11/23 07:29, Marc Millas wrote:
>> >      > Hi,
>>
>
please from psql do:
\l+  (That a lower case L)

on both databases.  I ran into this once because I had used the DEFAULT
COLLATION on one and a SPECIFIC Collation on the other machine.

That would explain it.

You set these things when you create the database.

Kirk...

^ permalink  raw  reply  [nested|flat] 27+ messages in thread

* Re: order by
  2023-05-11 14:29 order by Marc Millas <marc.millas@mokadb.com>
@ 2023-05-11 14:48 ` Adam Scott <adam.c.scott@gmail.com>
  2023-05-11 14:55   ` Re: order by Marc Millas <marc.millas@mokadb.com>
  2 siblings, 1 reply; 27+ messages in thread

From: Adam Scott @ 2023-05-11 14:48 UTC (permalink / raw)
  To: Marc Millas <marc.millas@mokadb.com>; +Cc: pgsql-general@lists.postgresql.org

Check the index creation has NULLS FIRST (or LAST) on both indexes that are
used. Use explain to see what indexes are used

See docs for create index:
https://www.postgresql.org/docs/current/sql-createindex.html

On Thu, May 11, 2023, 7:30 AM Marc Millas <marc.millas@mokadb.com> wrote:

> Hi,
>
> I keep on investigating on the "death postgres" subject
> but open a new thread as I don't know if it's related to my pb.
>
> I have 2 different clusters, on 2 different machines, one is prod, the
> second test.
> Same data volumes.
>
> On prod if I do
> select col_a, count(col_a) from table_a group by col_a order by col_a
> desc,
> I get the numbers of NULL on top.
> To get the number of NULL on top on the test db, I have to
> select col_a, count(col_a) from table_a group by col_a order by col_a asc.
>
> so, it looks like there is something different within the b-tree operator
> class of varchar (?!?)
> between those 2 clusters.
>
> What can I check to to explain this difference as, to my understanding,
> it's not a postgresql.conf parameter.
>
> thanks
>
> Marc MILLAS
> Senior Architect
> +33607850334
> www.mokadb.com
>
>

^ permalink  raw  reply  [nested|flat] 27+ messages in thread

* Re: order by
  2023-05-11 14:29 order by Marc Millas <marc.millas@mokadb.com>
  2023-05-11 14:48 ` Re: order by Adam Scott <adam.c.scott@gmail.com>
@ 2023-05-11 14:55   ` Marc Millas <marc.millas@mokadb.com>
  2023-05-11 21:08     ` Re: order by Ron <ronljohnsonjr@gmail.com>
  0 siblings, 1 reply; 27+ messages in thread

From: Marc Millas @ 2023-05-11 14:55 UTC (permalink / raw)
  To: Adam Scott <adam.c.scott@gmail.com>; +Cc: pgsql-general@lists.postgresql.org

Thanks,

I do know about index options.

that table have NO (zero) indexes.

Marc MILLAS
Senior Architect
+33607850334
www.mokadb.com



On Thu, May 11, 2023 at 4:48 PM Adam Scott <adam.c.scott@gmail.com> wrote:

> Check the index creation has NULLS FIRST (or LAST) on both indexes that
> are used. Use explain to see what indexes are used
>
> See docs for create index:
> https://www.postgresql.org/docs/current/sql-createindex.html
>
> On Thu, May 11, 2023, 7:30 AM Marc Millas <marc.millas@mokadb.com> wrote:
>
>> Hi,
>>
>> I keep on investigating on the "death postgres" subject
>> but open a new thread as I don't know if it's related to my pb.
>>
>> I have 2 different clusters, on 2 different machines, one is prod, the
>> second test.
>> Same data volumes.
>>
>> On prod if I do
>> select col_a, count(col_a) from table_a group by col_a order by col_a
>> desc,
>> I get the numbers of NULL on top.
>> To get the number of NULL on top on the test db, I have to
>> select col_a, count(col_a) from table_a group by col_a order by col_a asc.
>>
>> so, it looks like there is something different within the b-tree operator
>> class of varchar (?!?)
>> between those 2 clusters.
>>
>> What can I check to to explain this difference as, to my understanding,
>> it's not a postgresql.conf parameter.
>>
>> thanks
>>
>> Marc MILLAS
>> Senior Architect
>> +33607850334
>> www.mokadb.com
>>
>>

^ permalink  raw  reply  [nested|flat] 27+ messages in thread

* Re: order by
  2023-05-11 14:29 order by Marc Millas <marc.millas@mokadb.com>
  2023-05-11 14:48 ` Re: order by Adam Scott <adam.c.scott@gmail.com>
  2023-05-11 14:55   ` Re: order by Marc Millas <marc.millas@mokadb.com>
@ 2023-05-11 21:08     ` Ron <ronljohnsonjr@gmail.com>
  2023-05-13 16:56       ` Re: order by Marc Millas <marc.millas@mokadb.com>
  0 siblings, 1 reply; 27+ messages in thread

From: Ron @ 2023-05-11 21:08 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

On 5/11/23 09:55, Marc Millas wrote:
> Thanks,
>
> I do know about index options.
>
> that table have NO (zero) indexes.

If the table has no indices, then why did you write "it looks like there is 
something different within the *b-tree operator* class of varchar"?  After 
all, you only care about b-trees when you have b-tree indices.

-- 
Born in Arizona, moved to Babylonia.

^ permalink  raw  reply  [nested|flat] 27+ messages in thread

* Re: order by
  2023-05-11 14:29 order by Marc Millas <marc.millas@mokadb.com>
  2023-05-11 14:48 ` Re: order by Adam Scott <adam.c.scott@gmail.com>
  2023-05-11 14:55   ` Re: order by Marc Millas <marc.millas@mokadb.com>
  2023-05-11 21:08     ` Re: order by Ron <ronljohnsonjr@gmail.com>
@ 2023-05-13 16:56       ` Marc Millas <marc.millas@mokadb.com>
  0 siblings, 0 replies; 27+ messages in thread

From: Marc Millas @ 2023-05-13 16:56 UTC (permalink / raw)
  To: Ron <ronljohnsonjr@gmail.com>; +Cc: pgsql-general@lists.postgresql.org

On Thu, May 11, 2023 at 11:08 PM Ron <ronljohnsonjr@gmail.com> wrote:

> On 5/11/23 09:55, Marc Millas wrote:
>
> Thanks,
>
> I do know about index options.
>
> that table have NO (zero) indexes.
>
>
> If the table has no indices, then why did you write "it looks like there
> is something different within the *b-tree operator* class of varchar"?
> After all, you only care about b-trees when you have b-tree indices.
>
> to my understanding, the btree operator is the default operator used to do
any sort, like an order by, for varchar, text, .. types.

> --
> Born in Arizona, moved to Babylonia.
>

^ permalink  raw  reply  [nested|flat] 27+ messages in thread

* Re: order by
  2023-05-11 14:29 order by Marc Millas <marc.millas@mokadb.com>
@ 2023-05-11 21:12 ` Ron <ronljohnsonjr@gmail.com>
  2 siblings, 0 replies; 27+ messages in thread

From: Ron @ 2023-05-11 21:12 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

On 5/11/23 09:29, Marc Millas wrote:
> Hi,
>
> I keep on investigating on the "death postgres" subject
> but open a new thread as I don't know if it's related to my pb.
>
> I have 2 different clusters, on 2 different machines, one is prod, the 
> second test.
> Same data volumes.
>
> On prod if I do
> select col_a, count(col_a) from table_a group by col_a order by col_a desc,
> I get the numbers of NULL on top.
> To get the number of NULL on top on the test db, I have to
> select col_a, count(col_a) from table_a group by col_a order by col_a asc.

This doesn't answer your question, but: ORDER BY has NULLS { FIRST | LAST } 
options, so no need to completely change the sort order.

And this just confuses your question:
https://www.postgresql.org/docs/15/sql-select.html

> If|NULLS LAST|is specified, null values sort after all non-null values; 
> if|NULLS FIRST|is specified, null values sort before all non-null values. 
> If neither is specified, the default behavior is*|NULLS 
> LAST|**when**|ASC|****is specified *or implied, and*|NULLS 
> FIRST|**when**|DESC|****is specified* (thus, the default is to act as 
> though nulls are larger than non-nulls).


-- 
Born in Arizona, moved to Babylonia.

^ permalink  raw  reply  [nested|flat] 27+ messages in thread


end of thread, other threads:[~2023-05-13 16:56 UTC | newest]

Thread overview: 27+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-02-29 18:56 order by Adrian Perez Camarena <adrianp@ife.org.mx>
2000-03-01 09:18 ` Oleg Broytmann <phd@phd.russ.ru>
2006-11-14 22:03 ORDER BY MicroUser <a.shafar@gmail.com>
2006-11-15 19:50 ` Re: ORDER BY Ashley Moran <work@ashleymoran.me.uk>
2006-11-15 20:52 ` Re: ORDER BY Niklas Johansson <spot@tele2.se>
2006-11-15 22:28   ` Re: ORDER BY Brent Wood <b.wood@niwa.co.nz>
2006-11-15 21:35 ` Re: ORDER BY Alexander Staubo <alex@purefiction.net>
2006-11-15 22:59   ` Re: ORDER BY George Pavlov <gpavlov@mynewplace.com>
2006-11-15 23:53     ` Re: ORDER BY Chris Mulcahy <pgsql@cmulcahy.com>
2006-11-21 09:13       ` Re: ORDER BY Paefgen, Peter (LDS) <Peter.Paefgen@lds.nrw.de>
2006-11-21 15:45         ` Re: ORDER BY Shane Ambler <pgsql@007Marketing.com>
2006-11-16 06:43     ` Re: ORDER BY John Sidney-Woollett <johnsw@wardbrook.com>
2006-11-16 08:58     ` Re: ORDER BY Alban Hertroys <alban@magproductions.nl>
2006-11-16 10:04   ` Re: ORDER BY Harald Fuchs <hf1110x@protecting.net>
2006-11-21 03:05     ` Re: ORDER BY Gurjeet Singh <singh.gurjeet@gmail.com>
2023-05-11 14:29 order by Marc Millas <marc.millas@mokadb.com>
2023-05-11 14:43 ` Adrian Klaver <adrian.klaver@aklaver.com>
2023-05-11 15:00   ` Marc Millas <marc.millas@mokadb.com>
2023-05-11 15:23     ` Adrian Klaver <adrian.klaver@aklaver.com>
2023-05-11 15:29       ` Marc Millas <marc.millas@mokadb.com>
2023-05-11 15:37         ` Adrian Klaver <adrian.klaver@aklaver.com>
2023-05-11 22:42         ` Kirk Wolak <wolakk@gmail.com>
2023-05-11 14:48 ` Adam Scott <adam.c.scott@gmail.com>
2023-05-11 14:55   ` Marc Millas <marc.millas@mokadb.com>
2023-05-11 21:08     ` Ron <ronljohnsonjr@gmail.com>
2023-05-13 16:56       ` Marc Millas <marc.millas@mokadb.com>
2023-05-11 21:12 ` Ron <ronljohnsonjr@gmail.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