PostgreSQL hstore数组列的索引 [英] Indexes on PostgreSQL hstore array columns

问题描述

我知道您可以在hstore列中的字段上创建索引。
我知道你也可以在数组列上创建一个GIN索引。

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.

但是在hstore数组上创建索引的语法是什么？

But what is the syntax to create an index on an hstore array?

例如

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

假设客户购买hstore可能是哈希值

Let's say the customer purchases hstore may be a hash like

productId -> 1
price -> 9.99

我在customer_purchases hstore中有一系列数据[]

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

我想在customer.customer_purchases创建索引[] - > productId

I want to create an index on customer.customer_purchases[]-> productId

这可能吗？我尝试过不同的CREATE INDEX语法组合，但它们似乎都不支持hstore数组中的索引字段。

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.

推荐答案

我认为你误解了PostgreSQL 数组 s。 数组实际上只是一个字符串。你不能在数组中索引对象（在本例中为 HSTORE s），因为它不是 TABLE 。

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

另外，你为什么使用 HSTORE s在这里？

Also, Why are you using HSTOREs here?

如果必须根据<$ c $创建 INDEX c>购买 HSTORE 在这里，执行以下操作：

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

这只是练习理解 HSTORE 类型？或者你是否有一些真正的用例会使你真实数据的所有这些混淆值得吗？

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?

这篇关于PostgreSQL hstore数组列的索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！