From: | Anton Ananich <anton(dot)ananich(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | GIN Indexes: Extensibility |
Date: | 2016-07-27 07:28:45 |
Message-ID: | D9F2F865-AD43-4946-904C-2A160B96A81F@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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,
Anthony Ananich
From | Date | Subject | |
---|---|---|---|
Next Message | Marc Fournier | 2016-07-27 07:30:29 | Re: Uber migrated from Postgres to MySQL |
Previous Message | Chris Travers | 2016-07-27 07:17:58 | Re: Uber migrated from Postgres to MySQL |