[TOC] ## HSTORE 存储 - 当没有redis 时候,hstore 使用 pgsql 自带的方式存储 - 当有redis_fdw 扩展时,则会使用redis 的扩展 语法: ``` "key1=>value1"[, "key2=>value2", ...] ``` ## 添加扩展 ``` CREATE extension hstore; ``` ### 创建 HSTORE ``` CREATE TABLE product ( id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, product_name VARCHAR NOT NULL, attributes HSTORE ); ``` ### 插入数据 ``` INSERT INTO product (product_name, attributes) VALUES ('Computer A', 'CPU=>2.5, Memory=>16G, Disk=>1T'), ('Shirt B', 'Season=>Spring, Style=>Business, Color=>White') RETURNING *; ``` > 注意 key 是区分大小写的,查询时,按照插入时的key搜索 ### 查询 ``` > SELECT * FROM product; id | product_name | attributes ----+--------------+----------------------------------------------------------- 1 | Computer A | "CPU"=>"2.5", "Disk"=>"1T", "Memory"=>"16G" 2 | Shirt B | "Color"=>"White", "Style"=>"Business", "Season"=>"Spring" (2 rows) ``` 条件查询 ``` > SELECT * FROM product WHERE attributes['Memory'] = '16G'; id | product_name | attributes ----+--------------+--------------------------------------------- 1 | Computer A | "CPU"=>"2.5", "Disk"=>"1T", "Memory"=>"16G" (1 row) ``` 查询是否存在指定key ``` SELECT * FROM product WHERE attributes ? 'Color'; SELECT * FROM product WHERE attributes['Color'] IS NOT NULL; ``` ### 更新或添加(添加键值对) ``` UPDATE product SET attributes['Brand'] = 'HP' WHERE id = 1; ``` ### 更新(删除键值对) ``` UPDATE product SET attributes = delete(attributes, 'brand') WHERE id = 1 ```