Postgres Pro
Downloads
Home   >   mailing lists

Thread: GIN Indexes: Extensibility

GIN Indexes: Extensibility

From
Anton Ananich
Date:
Dear All,

Here is what I have:

user=# create table FOO (key jsonb);
CREATE TABLE
user=# insert into FOO(key) values ('[2014]'), ('[2015]'), ('[2016]'), ('[2014, 2]'), ('[2014, 2, 3]'), ('[2014, 3]'), ('[2014,2,4]'), ('[2014, 2,4]'), ('[2014,3,13]'), ('[2014, 2, 15]');
INSERT 0 10
user=# SELECT key FROM FOO order by key;
      key      
---------------
 [2014]
 [2015] <==
 [2016] <==
 [2014, 2]
 [2014, 3] <==
 [2014, 2, 3]
 [2014, 2, 4]
 [2014, 2, 4]
 [2014, 2, 15]
 [2014, 3, 13]
(10 rows)


In my situation this order is invalid. Obviously, year 2016 should go after 2014, like that:

      key      
---------------
 [2014]
 [2014, 2]
 [2014, 2, 3]
 [2014, 2, 4]
 [2014, 2, 4]
 [2014, 2, 15]
 [2014, 3] <==
 [2014, 3, 13]
 [2015] <==
 [2016] <==

This is a simplified example, my real application is much more complicated and sorted arrays could have tens of values, could even be arrays of arrays.For this reason I need to customize sort function.

I found that there is a way to customize function `int compare(Datum a, Datum b)` (proof link: https://www.postgresql.org/docs/9.5/static/gin-extensibility.html), but I found no example which shows how to use it.

I’d appreciate any information which could help me to achieve the described result, even if it is just a link to an existing example.

Regards,


Re: GIN Indexes: Extensibility

From
Vick Khera
Date:
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.

Re: GIN Indexes: Extensibility

From
Paul Jungwirth
Date:
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

Re: GIN Indexes: Extensibility

From
Anton Ananich
Date:
Vick, you are right. That’s why I’d wish to add some custom code to MY PostgreSQL instance and set such a sort order,
whichis optimal for my application. 

On Jul 27, 2016, at 17:44, Vick Khera <vivek@khera.org> 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.
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

Re: GIN Indexes: Extensibility

From
Anton Ananich
Date:
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