Indexes on PostgreSQL hstore array columns

Question

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.

This seems like a solved issue if you ignore hstore and simply use two extra tables. Why do it this way? If you must for some reason, define an IMMUTABLE function which produces a sortable value and call the function in your CREATE INDEX syntax. — David-SkyMesh, Apr 12 '12 at 03:46
As I mentioned in a comment below, we want to evolve towards a schema-less DB model, so that we can roll out new versions of our applications without the need for downtime caused by DB upgrades and ALTER TABLE ADD COLUMN table locks. — Jacek Furmankiewicz, Apr 12 '12 at 14:02

David-SkyMesh · Accepted Answer · 2012-04-12T04:28:26.947

5

I think you've misunderstood PostgreSQL Arrays. An Array is actually just a string. You can't index the objects (in this case HSTOREs) 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 HSTOREs 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?

edited Apr 12 '12 at 04:28

answered Apr 12 '12 at 04:14

David-SkyMesh

5,041
1
31
38

1

I don't know of any reason you can't create a GIN or GIST index directly on an hstore column; it is documented here: http://www.postgresql.org/docs/9.1/interactive/hstore.html#AEN133006 But I do tend to think it's a bad choice compared to normalizing the data with separate tables for purchases and line_items. Just because you *can* do something doesn't make it a good idea. On the other hand, if this is a training exercise to see how to use hstore, I think this answer may be the best way to go. – kgrittn Apr 12 '12 at 05:06
2

It's not an obfuscation. The need is for zero downtime upgrades (i.e. we cannot lock the table with ALTER TABLE ADD COLUMN) when deploying a new version of the app. Hstore seemed like a good option to basically have a schema-less entity storage that can evolve overtime via application logic without the need for DB upgrades – Jacek Furmankiewicz Apr 12 '12 at 13:47
2

Adding a column to a table in postgresql won't lock the table for any noticeable amount of time (assuming the column has no constraints and can be nullable). – Joe Van Dyk Jul 03 '13 at 22:41

Indexes on PostgreSQL hstore array columns

1 Answers1

Linked