New operators and class for jsonb with gin indexing
Hello all!
I am having problems applying gin indexing to my new operator class for
type jsonb.
I created an operator that can query(jsonb column) keys and dates where
dates are more than, less than, equal and not equal to the one provided
inside another jsonb.
Because I want to make it use index search and not sequential search, I
wanted to use gin indexing, but it says that i'm missing support functions.
Did anybody try to do something like this? I searched everywhere online,
and I didn't see an example of how they should look like. This is how i
created an operator class
CREATE OPERATOR CLASS custom_jsonb_dates_operators FOR TYPE jsonb
USING gin AS
OPERATOR 1 #>? (jsonb, jsonb),
OPERATOR 2 #<? (jsonb, jsonb),
OPERATOR 3 #=? (jsonb, jsonb),
OPERATOR 4 #!=? (jsonb, jsonb)
After this, when i created an index with this operator class, i got the
following error:
missing support function 2 for attribute 1 of index "idx_name"
I tried writing my own support functions, but none worked(after creating an
index, it just never returned any results.
Thanks,
Luka Zivkovic
Luka Zivkovic <lukazivkovic58@gmail.com> writes:
I am having problems applying gin indexing to my new operator class for
type jsonb.
I created an operator that can query(jsonb column) keys and dates where
dates are more than, less than, equal and not equal to the one provided
inside another jsonb.
Because I want to make it use index search and not sequential search, I
wanted to use gin indexing, but it says that i'm missing support functions.
Did anybody try to do something like this?
There's not a whole lot of documentation about this, but there is some:
https://www.postgresql.org/docs/13/gin-extensibility.html
After reading that, I'd suggest looking into the code for gin opclass
support in various contrib modules (hstore might be a good start).
regards, tom lane
Thank you for your response!
Yeah, i was looking i to that, and it looks like i just didn't implement
it correctly. I'm just wondering because I'm using it for jsonb type, can i
just use already implemented support functions for jsonb? I'm just not too
sure how those functions work
Thanks,
Luka Živković
On Tue, Feb 23, 2021, 16:28 Tom Lane <tgl@sss.pgh.pa.us> wrote:
Show quoted text
Luka Zivkovic <lukazivkovic58@gmail.com> writes:
I am having problems applying gin indexing to my new operator class for
type jsonb.
I created an operator that can query(jsonb column) keys and dates where
dates are more than, less than, equal and not equal to the one provided
inside another jsonb.
Because I want to make it use index search and not sequential search, I
wanted to use gin indexing, but it says that i'm missing supportfunctions.
Did anybody try to do something like this?
There's not a whole lot of documentation about this, but there is some:
https://www.postgresql.org/docs/13/gin-extensibility.html
After reading that, I'd suggest looking into the code for gin opclass
support in various contrib modules (hstore might be a good start).regards, tom lane
Luka Zivkovic <lukazivkovic58@gmail.com> writes:
Yeah, i was looking i to that, and it looks like i just didn't implement
it correctly. I'm just wondering because I'm using it for jsonb type, can i
just use already implemented support functions for jsonb? I'm just not too
sure how those functions work
The support functions need to know about the operators' semantics, so
you have to write your own when inventing new operators.
regards, tom lane