PostgreSQL 教程: hstore 数据类型

八月 23, 2023

摘要：在本教程中，您将学习如何使用 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" => "postgresqltutorial.com",
	   "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" => "postgresqltutorial.com",
"language"  => "English",
"ISBN-13"   => "978-1449370001",
"weight"    => "1 ounces"'
	);

从 hstore 列查询数据

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

SELECT
	attr
FROM
	books;

postgresql hstore query

查询特定键的值

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

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

postgresql hstore query key

在 WHERE 子句中使用值

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

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

postgresql hstore WHERE clause

将键值对添加到现有行

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

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

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

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

postgresql hstore add key-value

更新现有的键值对

您可以使用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';

postgesql hstore check key

检查键值对

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

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

postgresql hstore check key-pair

查询包含多个指定键的行

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

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

postgresql hstore check multiple keys

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

从 hstore 列获取所有键

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

SELECT
	akeys (attr)
FROM
	books;

postgresql hstore akeys function

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

SELECT
	skeys (attr)
FROM
	books;

postgresql hstore skeys function

获取 hstore 列中的所有值

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

SELECT
	avals (attr)
FROM
	books;

postgresql hstore avals function

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

SELECT
	svals (attr)
FROM
	books;

postgresql hstore svals

将 hstore 数据转换为 JSON

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

SELECT
  title,
  hstore_to_json (attr) json
FROM
  books;

postgresql hstore to json

将 hstore 数据转换为集合

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

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

postgresql hstore to sets

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

搜索

分类

标签