<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>
