Geek asks and answers

PostgreSQL hstore array column indices

I know that you can create an index in a field in the hstore column. I know that you can also create a GIN index in an array column.

But what is the syntax for creating an index in an hstore array?

eg.

CREATE TABLE customer (
    pk serial PRIMARY KEY,
    customer hstore,
    customer_purchases hstore[]
);

Say a buyer buying an hstore might be a hash, for example

productId -> 1
price -> 9.99

and I have an array of those in the store customer_purchases []

I want to create an index for customer.customer_purchases [] → productId

Is it possible? I tried different combinations of CREATE INDEX syntaxes, and none of them seem to support indexing fields in an hstore array.

+3
source share
1 answer

, PostgreSQL Array s. Array - . ( HSTORE s) , , 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)
);

, HSTORE ?

INDEX "purchase" HSTORE , :

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));

, HSTORE? - , ?

+5

More articles:


All Articles