如何在Postgres 9.3中的JSON字段上创建索引 [英] How to create index on json field in Postgres 9.3
问题描述
在PostgreSQL 9.3 Beta 2(?)中,如何在JSON字段上创建索引?我使用用于hstore
的->
运算符尝试了此操作,但收到以下错误:
In PostgreSQL 9.3 Beta 2 (?), how do I create an index on a JSON field? I tried it using the ->
operator used for hstore
but got the following error:
CREATE TABLE publishers(id INT, info JSON);
CREATE INDEX ON publishers((info->'name'));
错误:数据类型json没有访问方法的默认运算符类 "btree"提示:您必须为索引指定一个运算符类,或者 为数据类型定义默认的运算符类.
ERROR: data type json has no default operator class for access method "btree" HINT: You must specify an operator class for the index or define a default operator class for the data type.
推荐答案
发现:
CREATE TABLE publishers(id INT, info JSON);
CREATE INDEX ON publishers((info->>'name'));
如评论中所述,此处的细微差别是->>
而不是->
.前者以文本形式返回值,后者以JSON对象形式返回.
As stated in the comments, the subtle difference here is ->>
instead of ->
. The former one returns the value as text, the latter as a JSON object.
这篇关于如何在Postgres 9.3中的JSON字段上创建索引的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!