PostgreSQL 的 hstore、arrays 数据类型

发表于 | 分类 |
| 全文总计: 字
PostgreSQL 的 hstore、arrays 数据类型

PostgreSQL

官网地址：https://www.postgresql.org/

pgSQL 12.0 官网教程：https://www.postgresql.org/docs/12/index.html （PDF 版：postgresql-12-A4.pdf）

hstore 数据类型

hstore 官网教程：https://www.postgresql.org/docs/12/hstore.html

hstore 中文教程：http://www.postgres.cn/docs/12/hstore.html 或 https://www.sjkjc.com/postgresql/hstore-type/

辅助资料：https://wiki.postgresql.org/wiki/File:Pg-as-nosql-pgday-fosdem-2013.pdf （附件：Pg-as-nosql-pgday-fosdem-2013.pdf）

hstore 数据类型

Postgres hstore 是 PostgreSQL 数据库中的一种特殊数据类型，它允许我们以键值对的形式保存和操作任意数量的数据。

hstore 类型的数据可以在查询时更加方便地进行复杂的条件搜索和匹配。我们可以使用 hstore 数据类型来存储 JSON 数据、动态属性集、键值对配置等复杂数据结构。

PostgreSQL HSTORE 数据类型很适合存储无规则的字典值，比如电子产品的属性，服装的规格等。

hstore 数据格式

HSTORE 类型字段是存储 k-v 数据的集合，key 和 value 之间使用 => 进行映射。多个 k-v 对之间使用英文逗号分隔

1
"key1=>value1"[, "key2=>value2", ...]

这里：

  • "key1=>value1" 是一个键值对。 如果键和值中不包含空格，可以省略双引号。
  • 多个键值对之间使用逗号分隔。
  • 键和值都是文本值。

启用 hstore

PostgreSQL HSTORE 数据类型在 hstore 模块中实现。要使用 PostgreSQL HSTORE 类型，需要启用扩展：

1
CREATE EXTENSION hstore;

hstore 数据类型操作示例

库表数据准备

创建示例库表

1
2
3
4
5
6
7
8
9
CREATE TABLE "users" (
  "id" serial PRIMARY KEY,
  "username" varchar(255) NOT NULL,
  "config" hstore
);

COMMENT ON COLUMN "users"."id" IS '主键';
COMMENT ON COLUMN "users"."username" IS '用户名称';
COMMENT ON COLUMN "users"."config" IS '用户属性配置';

创建数据

1
2
3
4
5
6
7
8
9
10
INSERT INTO users ( username, config )
VALUES ('tom','"language"=>"Chinese","weight"=>"45 kg","email"=>"[email protected]"');
INSERT INTO users ( username, config )
VALUES ('jack','"language"=>"English","weight"=>"56 kg","email"=>"[email protected]"');
INSERT INTO users ( username, config )
VALUES ('rose','"language"=>"Chinese","weight"=>"66.6 kg","email"=>"[email protected]"');
INSERT INTO users ( username, config )
VALUES ('tony','"language"=>"English","weight"=>"50.2 kg","job"=>"barber"');
INSERT INTO users ( username, config )
VALUES ('jenny','"language"=>"Chinese","job"=>"chef","hight"=>"187"');

得到如下基础数据：

1
2
3
4
SELECT
	* 
FROM
	users;

查询 hstore 列

1
2
3
4
SELECT
	config 
FROM
	users;

查询指定 key 的数值

1
2
3
4
SELECT
	config -> 'email' AS user_email 
FROM
	users;

where 条件查询

1
2
3
4
5
6
SELECT
	* 
FROM
	users 
WHERE
	config -> 'language' = 'Chinese';

更新 k-v 对

方式1

1
2
3
4
UPDATE users 
set config = config || hstore('email', '[email protected]') 
WHERE 
	username = 'rose' returning *;

returning * 表示返回成功影响行数的所有列信息

方式2

1
2
3
4
UPDATE users 
set config['email'] = '[email protected]' 
WHERE 
	username = 'rose' returning *;

returning * 表示返回成功影响行数的所有列信息

插入 k-v 对

和更新语法一致

方式1

1
2
3
4
UPDATE users 
set config['address'] = 'beijing' 
WHERE 
	username = 'rose' RETURNING *;

returning * 表示返回成功影响行数的所有列信息

方式2

1
2
3
4
UPDATE users 
SET config = config || hstore ( 'address', 'shanghai' ) 
WHERE
	username = 'tom' RETURNING *;

returning * 表示返回成功影响行数的所有列信息

删除 k-v 对

1
2
3
4
UPDATE users 
SET config = DELETE ( config, 'address' ) 
WHERE
	username = 'tom' RETURNING *;

returning * 表示返回成功影响行数的所有列信息

查询 hstore 列含有指定 key

1
2
3
4
5
6
7
8
SELECT
	username,
  	config->'job' as job,
  	config	
FROM
	users 
WHERE
	config ? ['job', 'address'];

查询 hstore 列包含在指定 key 集合中

1
2
3
4
5
6
7
8
9
SELECT
	username,
  	config->'job' as job,
  	config->'address' as address,
  	config	
FROM
	users 
WHERE
	config ?| ARRAY['job', 'address'];

?| ARRAY['job', 'address'] 表示查询记录的 hstore 字段中 key 是否包含在指定字符串数组中。

查询 hstore 列全量包含指定 key 集合

1
2
3
4
5
6
7
8
9
SELECT
	username,
    config->'email' as email,
	config->'address' as address,
  config	
FROM
	users 
WHERE
	config ?& ARRAY['email', 'address'];

?& ARRAY['job', 'address'] 表示查询记录的 hstore 字段中 key 必须被指定字符串数组中所有元素包含。

查询 hstore 列所有 key

方式1

1
2
3
4
5
SELECT
	username,
	akeys ( config ) 
FROM
	users;

方式2

1
2
3
4
5
SELECT
	username,
	skeys ( config ) 
FROM
	users;

查询 hstore 列所有 key 并去重

1
2
3
4
SELECT DISTINCT UNNEST
	( akeys ( config ) ) 
FROM
	users;

查询 hstore 列所有 value

方式1

1
2
3
4
5
SELECT
	username,
	avals ( config ) 
FROM
	users;

方式2

1
2
3
4
5
SELECT
	username,
	svals ( config ) 
FROM
	users;

查询 hstore 列所有 value 并去重

1
2
3
4
SELECT DISTINCT UNNEST
	( avals ( config ) ) 
FROM
	users;

合并 k-v 对

1
2
3
4
5
6
7
8
SELECT
	username,
  config ::hstore || '"email"=>"tom"' ::hstore as newconfig,
  config	
FROM
	users 
WHERE
 username = 'jack'

注意：||从左到右进行合并，如果 key 相同则后者覆盖掉前者 

1
2
3
4
5
6
7
8
SELECT
	username,
  '"email"=>"tom"' ::hstore || config ::hstore as newconfig,
  config	
FROM
	users 
WHERE
 username = 'jack'

查询 hstore 列表包含指定 k-v 对

查询包含：”language”=>”English” 的记录

1
2
3
4
5
6
7
SELECT
	username,
  config	
FROM
	users 
WHERE
 config ::hstore @> '"language"=>"English"' ::hstore

过滤指定 key 集合

1
2
3
4
5
6
7
8
SELECT
	username,
  config ::hstore - ARRAY['hight','language'] as newconfig,
	config
FROM
	users 
WHERE 
  username = 'jenny'

过滤指定 key

1
2
3
4
5
6
7
8
SELECT
	username,
  config ::hstore - 'hight' ::text as newconfig,
	config
FROM
	users 
WHERE 
  username = 'jenny'

Arrays 数据类型

Arrays 官网教程：https://www.postgresql.org/docs/12/arrays.html

hstore 中文教程：http://www.postgres.cn/docs/12/arrays.html 或 https://www.sjkjc.com/postgresql/array-type/

arrays 列增加新元素

方式1

1
2
3
UPDATE user_hobbies
SET hobbies = ARRAY_APPEND(hobbies, 'draw')
WHERE username = 'rose' RETURNING *;

方式2

等同于：array_cat（hobbies, ARRAY['cook'])

1
2
3
UPDATE user_hobbies
SET hobbies = hobbies || ARRAY['cook']
WHERE username = 'rose' RETURNING *;

查看 arrays 列

1
2
3
4
5
SELECT
    username,
    hobbies
FROM
    user_hobbies;

操作数组类型列

arrays 列中元素是否存在指定元素

1
2
3
4
5
6
7
SELECT
  username,
  hobbies
FROM
  user_hobbies
WHERE
  'football' = ANY (hobbies);

arrays 列中元素是否存在于指定数组

使用 && 运算符来检查两个数组是否有交集，如果有交集，就表示数组中的任一元素存在于目标数组中。

1
2
3
4
5
6
7
SELECT
  username,
  hobbies
FROM
  user_hobbies
WHERE
  array['football', 'dance'] && hobbies;

arrays 列中元素均不存在于指定数组

1
2
3
4
5
6
7
SELECT
  username,
  hobbies
FROM
  user_hobbies
WHERE
  not(array['basketball', 'football'] && hobbies);

指定数组是否为 arrays 列的子集

1
2
3
4
5
6
7
SELECT
  username,
  hobbies
FROM
  user_hobbies
WHERE
  array['dance', 'cook'] <@ hobbies;

arrays 列是否为指定数组的子集

1
2
3
4
5
6
7
SELECT
  username,
  hobbies
FROM
  user_hobbies
WHERE
  array['basketball', 'football'] @> hobbies;

arrays 列中移除指定元素

1
2
3
4
UPDATE user_hobbies 
SET hobbies = array_remove ( hobbies, 'cook' ) 
WHERE
	username = 'rose' RETURNING *;

参考资料

https://www.sjkjc.com/postgresql/hstore-type/

https://geek-docs.com/postgresql/postgresql-questions/455_postgresql_postgres_hstore_gin_vs_gist_index_performance.html

http://www.freeoa.net/osuport/db/pg-type-hstore-jsonb_3367.html

https://www.postgresqltutorial.com/postgresql-tutorial/postgresql-hstore/

https://www.dbvis.com/thetable/storing-unstructured-data-with-hstore-in-postgresql/

https://blog.csdn.net/neweastsun/article/details/92849375

updated updated 2023-12-27 2023-12-27
本文结束感谢阅读

本文标题:PostgreSQL 的 hstore、arrays 数据类型

本文作者:woodwhales

原始链接:https://woodwhales.cn/2023/12/27/094/

许可协议: 署名-非商业性使用-禁止演绎 4.0 国际 转载请保留原文链接及作者。

0%