Indexes on PostgreSQL hstore array columns

hstore, indexing, postgresql

Solution

I think you've misunderstood PostgreSQL `Array`s. An `Array` is actually just a string. You can't index the objects (in this case `HSTORE`s) in the array, simply because it's not a `TABLE`.

Instead, create an extra table:

CREATE TABLE customer (
    pk bigserial PRIMARY KEY,
    customer hstore
);

CREATE TABLE purchases (
    pk bigserial PRIMARY KEY,
    customer_pk bigint not null,
    purchase hstore not null,
    constraint "must be a valid customer!" 
        foreign key (customer_pk) references customer(pk)
);

Also, Why are you using `HSTORE`s here?

If you must create an `INDEX` based on the `"purchase"` `HSTORE` here, do something like this:

CREATE OR REPLACE FUNCTION purchase_amount(purchase hstore) returns float as $$
    select ($1 -> 'price')::float;
$$ language 'SQL' IMMUTABLE;

CREATE INDEX "purchases by price" ON purchases (purchase_amount(purchase));

Is this just an exercise to understand the `HSTORE` type? or do you have some real use case that would make all this obfuscation of your real data worthwhile?

Problem

I know you can create an index on a field in a hstore column. I know you can also create a GIN index on a array column. But what is the syntax to create an index on an hstore array? e.g. ``` CREATE TABLE customer ( pk serial PRIMARY KEY, customer hstore, customer_purchases hstore[] ); ``` Let's say the customer purchases hstore may be a hash like ``` productId -> 1 price -> 9.99 ``` and I have an array of those in the customer_purchases hstore[] I want to create an index on customer.customer_purchases[]-> productId Is this possible? I've tried different combinations of CREATE INDEX syntaxes and none of them seem to support indexing fields in an hstore array.

Original source