PostgreSQL 教程: hstore 数据类型

八月 23, 2023

摘要：在本教程中，您将学习如何使用 PostgreSQL hstore 数据类型。

目录

PostgreSQL hstore 类型简介

hstore 模块实现 hstore 数据类型，用于将键值对存储在单个值中。

hstore 数据类型在许多情况下非常有用，例如半结构化数据或具有许多属性但很少被查询的行。请注意，键和值只是文本字符串。

启用 PostgreSQL hstore 扩展

在使用 hstore 数据类型之前，您需要启用 hstore 扩展，将 contrib 模块加载到您的 PostgreSQL 实例。

CREATE EXTENSION hstore;

创建一个带有 hstore 类型列的表

我们创建一个名为books的表，该表包含三列：

  • id是识别书本的主键。
  • title为产品的标题。
  • attr存储书籍的属性，例如 ISBN、重量和平装本。attr列的数据类型是 hstore。

我们使用 CREATE TABLE 语句来创建books表，如下所示：

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

将数据插入 PostgreSQL hstore 列

我们使用INSERT语句将数据插入到 hstore 列中，如下所示：

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

我们插入到 hstore 列中的数据是逗号分隔的 key => value 对的列表。键和值都使用双引号 (“”) 引起来。

让我们再插入一行。

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

从 hstore 列查询数据

使用SELECT语句从 hstore 列中查询数据与从具有原生数据类型的列中查询数据类似，如下：

SELECT attr FROM books;

输出：

                                                             attr
------------------------------------------------------------------------------------------------------------------------------
 "weight"=>"11.2 ounces", "ISBN-13"=>"978-1449370000", "language"=>"English", "paperback"=>"243", "publisher"=>"rockdata.net"
 "weight"=>"1 ounces", "ISBN-13"=>"978-1449370001", "language"=>"English", "paperback"=>"5", "publisher"=>"rockdata.net"
(2 rows)

查询特定键的值

PostgreSQL hstore 提供了从 hstore 列查询特定键的值的->运算符。例如，如果我们想知道books表中所有可用书籍的 ISBN-13，我们可以使用如下->运算符：

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

输出：

      isbn
----------------
 978-1449370000
 978-1449370001
(2 rows)

在 WHERE 子句中使用值

您可以在WHERE子句中使用->运算符来过滤 hstore 列的值与输入值匹配的行。例如，以下查询检索具有ISBN-13值匹配978-1449370000的书籍的title和weight:

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

输出：

        title        |   weight
---------------------+-------------
 PostgreSQL Tutorial | 11.2 ounces
(1 row)

将键值对添加到现有行

使用 hstore 列，您可以轻松地将新的键值对添加到现有行，例如，您可以将免费送货键添加到books表的attr列，如下所示：

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

现在，您可以检查"freeshipping" => "yes"对是否已成功添加。

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

输出：

         title          | freeshipping
------------------------+--------------
 PostgreSQL Tutorial    | yes
 PostgreSQL Cheat Sheet | yes
(2 rows)

更新现有的键值对

您可以使用UPDATE语句更新现有的键值对。以下语句将"freeshipping"键的值更新为"no"。

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

删除现有的键值对

PostgreSQL 允许您从 hstore 列中删除现有的键值对。例如，以下语句删除attr列中的键值对"freeshipping"=>"no"。

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

检查 hstore 列中的特定键

您可以在WHERE子句中使用?运算符检查 hstore 列中的特定键。例如，以下语句返回attr列包含publisher键的所有行。

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

输出：

         title          |  publisher   |                                                             attr
------------------------+--------------+------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL Tutorial    | rockdata.net | "weight"=>"11.2 ounces", "ISBN-13"=>"978-1449370000", "language"=>"English", "paperback"=>"243", "publisher"=>"rockdata.net"
 PostgreSQL Cheat Sheet | rockdata.net | "weight"=>"1 ounces", "ISBN-13"=>"978-1449370001", "language"=>"English", "paperback"=>"5", "publisher"=>"rockdata.net"
(2 rows)

检查键值对

您可以使用@>运算符根据 hstore 键值对进行查询。以下语句检索attr列包含与"weight"=>"11.2 ounces"匹配的键值对的所有行。

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

输出：

        title
---------------------
 PostgreSQL Tutorial
(1 row)

查询包含多个指定键的行

您可以使用?&运算符查询 hstore 列包含多个键的行。例如，您可以获得attr列同时包含language和weight键的书籍。

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

输出：

         title
------------------------
 PostgreSQL Tutorial
 PostgreSQL Cheat Sheet
(2 rows)

要检查其 hstore 列是否包含键列表中的任何键的行，请使用?|运算符而不是?&运算符。

从 hstore 列获取所有键

要从 hstore 列获取所有键，请使用如下的akeys()函数：

SELECT
	akeys (attr)
FROM
	books;

输出：

                     akeys
-----------------------------------------------
 {weight,ISBN-13,language,paperback,publisher}
 {weight,ISBN-13,language,paperback,publisher}
(2 rows)

或者，如果您希望 PostgreSQL 将结果作为集合返回，则可以使用skey()函数。

SELECT
	skeys (attr)
FROM
	books;

输出：

   skeys
-----------
 weight
 ISBN-13
 language
 paperback
 publisher
 weight
 ISBN-13
 language
 paperback
 publisher
(10 rows)

获取 hstore 列中的所有值

与键一样，您可以使用数组形式的avals()函数从 hstore 列中获取所有值。

SELECT
	avals (attr)
FROM
	books;

输出：

                          avals
---------------------------------------------------------
 {"11.2 ounces",978-1449370000,English,243,rockdata.net}
 {"1 ounces",978-1449370001,English,5,rockdata.net}
(2 rows)

或者，如果您想获得一组结果，则可以使用svals()函数。

SELECT
	svals (attr)
FROM
	books;

输出：

     svals
----------------
 11.2 ounces
 978-1449370000
 English
 243
 rockdata.net
 1 ounces
 978-1449370001
 English
 5
 rockdata.net
(10 rows)

将 hstore 数据转换为 JSON

PostgreSQL 提供了将 hstore 数据转换为 JSON 的hstore_to_json()函数。请查看下面语句：

SELECT
  title,
  hstore_to_json (attr) json
FROM
  books;

输出：

         title          |                                                              json
------------------------+--------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL Tutorial    | {"weight": "11.2 ounces", "ISBN-13": "978-1449370000", "language": "English", "paperback": "243", "publisher": "rockdata.net"}
 PostgreSQL Cheat Sheet | {"weight": "1 ounces", "ISBN-13": "978-1449370001", "language": "English", "paperback": "5", "publisher": "rockdata.net"}
(2 rows)

将 hstore 数据转换为集合

要将 hstore 数据转换为集合，请使用如下的each()函数：

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

输出：

         title          |    key    |     value
------------------------+-----------+----------------
 PostgreSQL Tutorial    | weight    | 11.2 ounces
 PostgreSQL Tutorial    | ISBN-13   | 978-1449370000
 PostgreSQL Tutorial    | language  | English
 PostgreSQL Tutorial    | paperback | 243
 PostgreSQL Tutorial    | publisher | rockdata.net
 PostgreSQL Cheat Sheet | weight    | 1 ounces
 PostgreSQL Cheat Sheet | ISBN-13   | 978-1449370001
 PostgreSQL Cheat Sheet | language  | English
 PostgreSQL Cheat Sheet | paperback | 5
 PostgreSQL Cheat Sheet | publisher | rockdata.net
(10 rows)

在本教程中，我们向您展示了如何使用 PostgreSQL hstore 数据类型，并向您介绍了可以对 hstore 数据类型执行的最有用的操作。

了解更多

PostgreSQL 教程：数据类型

PostgreSQL 文档：hstore 扩展

搜索

分类

标签