Re: GIN Indexes: Extensibility - Mailing list pgsql-general

From Anton Ananich
Subject Re: GIN Indexes: Extensibility
Date
Msg-id C6DA9CBF-215C-4AAE-80CF-C14D15751A89@gmail.com
Whole thread Raw
In response to Re: GIN Indexes: Extensibility  (Paul Jungwirth <pj@illuminatedcomputing.com>)
List pgsql-general
Paul,

This is a really valuable idea. It will work in some situations for me. But in other situations I do not know if table will have a key of type int[] or string[] or even mixed. That’s why I’d wish to use JSON arrays and customize sort ordering.

Anyway I appreciate you shared this approach!
Regards,
Anthony Ananich

On Jul 27, 2016, at 18:00, Paul Jungwirth <pj@illuminatedcomputing.com> wrote:

On 07/27/2016 07:44 AM, Vick Khera wrote:
On Wed, Jul 27, 2016 at 3:28 AM, Anton Ananich <anton.ananich@gmail.com> wrote:
In my situation this order is invalid. Obviously, year 2016 should go after
2014, like that:

I think you expect JSONB to sort differently than it does. I cannot
imagine what a "natural" ordering of arbitrary JSON objects is.

FWIW, Postgres arrays do sort in the way he's expecting:

paul=# create table t (id integer, v integer[]);
CREATE TABLE
paul=# insert into t values (1, array[2014]), (2, array[2014, 1]), (3, array[2016]);
INSERT 0 3
paul=# select * from t order by v;
id |    v
----+----------
 1 | {2014}
 2 | {2014,1}
 3 | {2016}
(3 rows)

So maybe convert to an array before sorting?

Paul



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

pgsql-general by date:

Previous
From: Anton Ananich
Date:
Subject: Re: GIN Indexes: Extensibility
Next
From: Markus Kolb
Date:
Subject: Multiple NOTIFY is ignored