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的各种操作符。

本文参考链接：https://blog.csdn.net/neweastsun/article/details/92849375