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.htmlhttps://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.htmlhttps://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 2024-09-14 2024-09-14
本文结束感谢阅读

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

本文作者:woodwhales

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

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

0%