PostgreSQL hstore数据类型

PostgreSQL hstore 数据类型

本文我们学习PostgreSQL hstore数据类型。

hstore模块实现hstore数据类型用于在单值中存储键值对。hstore数据类型在很多场景中非常有用，如半结构化数据或有很多属性却很少被查询的行。注意键值对仅能为文本字符串。

启用PostgreSQL hstore扩展

使用hstore类型你需要启用hstore扩展，在PostgreSQL实例中加载contrib模块。

CREATE EXTENSION hstore;

创建包括hstore数据类型的表

下面创建books表，共三个字段：

• id 是主键，唯一表示book.
• title 是标题
• attr 存储book的多个属性，如 ISBN, weight, paperback. attr列的数据类型为hstore.

创建语句为：

CREATE TABLE books (
   id serial primary key,
   title VARCHAR (255),
   attr hstore
);

插入数据至PostgreSQL hstore列

使用insert语句：

INSERT INTO books (title, attr)
VALUES
   (
      'PostgreSQL Tutorial',
      '"paperback" => "243",
      "publisher" => "postgresqltutorial.com",
      "language"  => "English",
      "ISBN-13"   => "978-1449370000",
       "weight"   => "11.2 ounces"'
   );

插入hstore列的数据是一组逗号分隔key =>value对，其中key和value都需要使用“”括起来。

下面再插入一行数据：

INSERT INTO books (title, attr)
VALUES
   (
      'PostgreSQL Cheat Sheet',
      '
"paperback" => "5",
"publisher" => "postgresqltutorial.com",
"language"  => "English",
"ISBN-13"   => "978-1449370001",
"weight"    => "1 ounces"'
   );

查询hstore列

从hstore列查询数据类似从基本数据类型列查询数据，使用select语句：

SELECT
   attr
FROM
   books;

查询特定key的值

PostgreSql hstore提供->操作符，用于查询hstroe列中特定key的值。举例，如果我们想获取所有book记录的isbn-13值，可以使用->操作符，代码如下：

SELECT
   attr -> 'ISBN-13' AS isbn
FROM
   books;

where子句中使用

我们也可以在where子句中使用->操作符过滤行包含特定值。举例，我们需要查询ISBN-13的值为978-1449370000的book，并返回其title和weight字段：

SELECT
   attr -> 'weight' AS weight
FROM
   books
WHERE
   attr -> 'ISBN-13' = '978-1449370000';

记录中增加key-value

对于hstore列，可以很容易给现有记录增加key-value，下面示例增加freeshipping"=>“yes”：

UPDATE books
SET attr = attr || '"freeshipping"=>"yes"' :: hstore;

现在我们检查看是否存在"freeshipping"=>“yes”：

SELECT
   title,
        attr -> 'freeshipping' AS freeshipping
FROM
   books;

更新记录key-value对

我们可以使用update语句更新记录的key-value对，下面语句更新freeshipping的值为no：

UPDATE books
SET attr = attr || '"freeshipping"=>"no"' :: hstore;

删除记录key-value对

PostgreSQL 支持删除hstore列中的key-value对。举例，下面语句删除attr列的"freeshipping"=>“no”：

UPDATE books 
SET attr = delete(attr, 'freeshipping');

检查hstore列中特定key

使用？操作符在where子句中可以检查hstore列中是否包含特定key。举例，下面语句返回所有attr列中包括publisher属性的行：

SELECT
  title,
  attr->'publisher' as publisher,
  attr
FROM
   books
WHERE
   attr ? 'publisher';

检查hstore列中特定key-value对

使用@>操作符检查hstore列中key-value对。下面语句返回attr列包含"weight"=>"11.2 ounces"的行记录：

SELECT
   title
FROM
   books
WHERE
   attr @> '"weight"=>"11.2 ounces"' :: hstore;

查询包含多个特定key的记录

使用?&操作符查询hstore列包含多个特定key的记录。举例，下面语句查询attr同时包括language 和 weight键的记录：

SELECT
   title
FROM
   books
WHERE
   attr ?& ARRAY [ 'language', 'weight' ];

如果你想实现包含其中任何一个，则使用?|操作符代替?&。

返回hstore列所有key

通过akeys()函数返回hstore列中所有key：

SELECT
   akeys (attr)
FROM
   books;

或者使用skeys()函数返回key集合：

SELECT
   skeys (attr)
FROM
   books;

返回hstore列所有value

和返回所有键一样，可以通过avals()函数返回所有值：

SELECT
   avals (attr)
FROM
   books;

或者使用svals()函数返回结果集：

SELECT
   svals (attr)
FROM
   books;

转换hstore为json

PostgreSQL 提供 hstore_to_json() 函数把hstore列数据转为json：

SELECT
  title,
  hstore_to_json (attr) json
FROM
  books;

转换hstore为集合

each()函数转换hstore数据为结果集：

SELECT
   title,
   (EACH(attr) ).*
FROM
   books;

总结

本文我们学习了PostgreSQL 中hstore数据类型，并介绍了操作hstore的各种操作符。