[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 ```