提要:在本教程中,我们将向您展示如何使用 PostgreSQL HSTORE 数据类型。

hstore 模块实现了将键/值对存储到单个值的 HSTORE 数据类型。注意,HSTORE 中的 都只能是字符串。

启用 hstore 模块

使用 HSTORE 数据类型之前,需要先启用 hstore 模块:

CREATE EXTENSION hstore;

创建包含 HSTORE 数据类型的表

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

插入数据

INSERT INTO books (title, attr)
VALUES
 (
 'PostgreSQL 轻松学',
 '"paperback" => "685",
    "publisher" => "sjk66.com",
    "language"  => "简体中文",
    "ISBN-13"   => "1234567890123",
 "weight"    => "412 克"'
 );

查询指定键的数据

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

在 WHERE 中使用键

SELECT
 attr -> 'weight' AS weight
FROM
 books
WHERE
 attr -> 'ISBN-13' = '1234567890123'

给已存在的数据添加键值对

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

更新键值对

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

删除键值对

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

判断 HSTORE 字段是否包含某个键

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

判断是否含有某键值对

SELECT
 title
FROM
 books
WHERE
 attr @> '"weight"=>"412 克"' :: hstore;

判断是否包含多个键

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

获取所有键

SELECT
 akeys (attr)
FROM
 books;

SELECT
 skeys (attr)
FROM
 books;

获取所有值

SELECT
 avals (attr)
FROM
 books;

SELECT
 svals (attr)
FROM
 books;

转换成 JSON

SELECT
  title,
  hstore_to_json (attr) json
FROM
  books;

转换成记录集

SELECT
 title,
 (EACH(attr) ).*
FROM
 books;
下一篇:PostgreSQL JSON类型