Re: [GENERAL] GIN Indexes: Extensibility
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
http://ananich.pro
On Jul 27, 2016, at 18:00, Paul Jungwirth <p...@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.anan...@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
Re: [GENERAL] GIN Indexes: Extensibility
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, which is optimal for my application. On Jul 27, 2016, at 17:44, Vick Khera <vi...@khera.org> wrote: > On Wed, Jul 27, 2016 at 3:28 AM, Anton Ananich <anton.anan...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] GIN Indexes: Extensibility
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
