PostgreSQL GIN Index on VARCHAR and JSONB

postgresql

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 _varchar_ops:

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?

Best Answer

Two indexes

Simply create two separate indexes. PostgreSQL will use both where appropriate.

CREATE INDEX ON table1 (account);
CREATE INDEX ON table1 USING GIN (json);

Using an extension

Or you can use the btree_gin.

btree_gin provides sample GIN operator classes that implement B-tree equivalent behavior for the data types int2, int4, int8, float4, float8, timestamp with time zone, timestamp without time zone, time with time zone, time without time zone, date, interval, oid, money, "char", varchar, text, bytea, bit, varbit, macaddr, inet, and cidr.

It looks like this,

CREATE EXTENSION btree_gin;
CREATE INDEX ON table1 USING gin (account,json);

Under normal circumstances, I'd likely use two indexes.

Related Question