I have a table with schema along the lines of:
CREATE TABLE table1( account VARCHAR(64) NOT NULL, json JSONB NOT NULL );
And I'd like to create an index on it that will allow me to run queries such as:
SELECT * FROM table1 WHERE account = :account AND json ... -- Some JSON search expression
So I tried adding an index on this table like this:
CREATE INDEX index1 ON table1 USING GIN (account, json);
And I'm getting:
ERROR: data type character varying has no default operator class for access method "gin" Hint: You must specify an operator class for the index or define a default operator class for the data type.
I've tried adding
CREATE INDEX index1 ON table1 USING GIN (account _varchar_ops, json);
And then I got:
ERROR: operator class "_varchar_ops" does not accept data type character varying
I realize I can simply add the account as a field of every json document, but I'd like to avoid this duplicity and potential gotcha or room for discrepancy, and just have the account as a column.
How can I solve this and get a combined index on these two fields?