Re: Custom column ordering

<font face=3D"Default Sans Serif,Verdana,Arial,Helvetica,sans-serif" size=
=3D"2">Hi Emre,Thanks for your tips! I think I'll do as you suggest=
 and keep it simple with a single ordering operator.Although, I do =
find your output very strange. You wrote ORDER BY port, which is a text typ=
e. Why does Postgres order using the ordering operators of the "Port" data =
type rather than the "text" type, even though you haven't performed a cast?=
Steven-----Emre Hasegeli <emre@h=
asegeli.com> wrote: -----<div st=
yle=3D"padding-right:0px;padding-left:5px;border-left:solid black 2px;">To:=
 Steven Xu <stevenx@yorku.ca>From: Emre Hasegeli <emre@hasegel=
i.com>Date: 03/05/2016 09:30AMCc: "pgsql-general@postgresql.org"=
 <pgsql-general@postgresql.org>Subject: Re: [GENERAL] Custom colu=
mn ordering<font face=3D"Courier New,Courier,monospace" size=
=3D"2">>   - Why is PostgreSQL not using the functional index I cre=
ated and why is it> not being ordered correctly?Your example=
 works for me:> hasegeli=3D# CREATE TABLE device=5Fport (port te=
xt);> CREATE TABLE>> hasegeli=3D# CREATE INDEX idx=5Fd=
evice=5Fport=5Fport=5Fproper ON device=5Fport (cast=5Fto=5Fport(port) port=
=5Fops DESC);> CREATE INDEX>> hasegeli=3D# INSERT INTO=
 device=5Fport VALUES ('a'), ('b'), ('c');> INSERT 0 3>&g=
t; hasegeli=3D# SELECT port FROM device=5Fport ORDER BY port;> port<=
br>> ------> c> b> a> (3 rows)>&g=
t; hasegeli=3D# SET enable=5Fseqscan =3D 0;> SET>> has=
egeli=3D# EXPLAIN ANALYZE SELECT port FROM device=5Fport ORDER BY cast=5Fto=
=5Fport(port);>               &nb=
sp;                     &=
nbsp;                    =
              QUERY PLAN> -------=
---------------------------------------------------------------------------=
----------------------------------------------------------------------&=
gt;  Index Scan Backward using idx=5Fdevice=5Fport=5Fport=5Fproper on =
device=5Fport  (cost=3D0.15..408.55 rows=3D1360 width=3D32) (actual ti=
me=3D0.042..0.053 rows=3D3 loops=3D1)>  Planning time: 0.079 ms=
>  Execution time: 0.079 ms> (3 rows)>  =
 - Is creating a separate data type and using a functional index on the=
> casts to this data type the right approach to a custom ordering?<b=
r>You don't need to create a type for this.  You can just create a=
non-default operator class and use it with your text type by specifythe=
 operator with ORDER BY ... USING clause.> Creating the index:<b=
r>> CREATE INDEX idx=5Fdevice=5Fport=5Fport=5Fproper on device=5Fport (c=
ast=5Fto=5Fport(port) port=5Fops desc);The operator class is not ne=
cessary in here as it is the default forthe "port" type.  DESC als=
o wouldn't make any difference.</fo=
nt>