PostgreSQL的hstore初步学习-阿里云开发者社区

开发者社区> 数据库> 正文

PostgreSQL的hstore初步学习

简介:

安装hstore:

进入源代码的 /contrib/hstore 目录,然后执行gmake 和 gmake install:

复制代码
[root@pg200 hstore]# gmake
gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fpic -I. -I. -I../../src/include -D_GNU_SOURCE   -c -o hstore_io.o hstore_io.c
gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fpic -I. -I. -I../../src/include -D_GNU_SOURCE   -c -o hstore_op.o hstore_op.c
gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fpic -I. -I. -I../../src/include -D_GNU_SOURCE   -c -o hstore_gist.o hstore_gist.c
gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fpic -I. -I. -I../../src/include -D_GNU_SOURCE   -c -o hstore_gin.o hstore_gin.c
gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fpic -I. -I. -I../../src/include -D_GNU_SOURCE   -c -o hstore_compat.o hstore_compat.c
gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fpic -I. -I. -I../../src/include -D_GNU_SOURCE   -c -o crc32.o crc32.c
gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fpic -shared -o hstore.so hstore_io.o hstore_op.o hstore_gist.o hstore_gin.o hstore_compat.o crc32.o -L../../src/port  -Wl,-rpath,'/usr/local/pgsql/lib',--enable-new-dtags 
复制代码

gmake:

复制代码
[root@pg200 hstore]# gmake install
/bin/mkdir -p '/usr/local/pgsql/lib'
/bin/mkdir -p '/usr/local/pgsql/share/extension'
/bin/mkdir -p '/usr/local/pgsql/share/extension'
/bin/sh ../../config/install-sh -c -m 755  hstore.so '/usr/local/pgsql/lib/hstore.so'
/bin/sh ../../config/install-sh -c -m 644 ./hstore.control '/usr/local/pgsql/share/extension/'
/bin/sh ../../config/install-sh -c -m 644 ./hstore--1.1.sql ./hstore--1.0--1.1.sql ./hstore--unpackaged--1.0.sql  '/usr/local/pgsql/share/extension/'
[root@pg200 hstore]# 
复制代码

 

然后,启动PostgreSQL,再启动psql后,安装hstore扩展:

postgres=# create extension hstore;
CREATE EXTENSION
postgres=# 

 

进行测试:

建表:

postgres=# create table hstore_test(item_id serial, data hstore);
NOTICE:  CREATE TABLE will create implicit sequence "hstore_test_item_id_seq" for serial column "hstore_test.item_id"
CREATE TABLE
postgres=# 

 

插入数据:

复制代码
postgres=# INSERT INTO hstore_test (data) VALUES ('"key1"=>"value1", "key2"=>"value2", "key3"=>"value3"');
INSERT 0 1
postgres=# select * from hstore_test;
 item_id |                         data                         
---------+------------------------------------------------------
       1 | "key1"=>"value1", "key2"=>"value2", "key3"=>"value3"
(1 row)

postgres=# 
复制代码

 

修改数据:

复制代码
postgres=# UPDATE hstore_test SET data = delete(data, 'key2')
postgres-# ;
UPDATE 1
postgres=# select * from hstore_test;
 item_id |                data                
---------+------------------------------------
       1 | "key1"=>"value1", "key3"=>"value3"
(1 row)

postgres=# 
复制代码

 

复制代码
postgres=# UPDATE hstore_test SET data = data || '"key4"=>"some value"'::hstore;
UPDATE 1
postgres=# select * from hstore_test;
 item_id |                           data                           
---------+----------------------------------------------------------
       1 | "key1"=>"value1", "key3"=>"value3", "key4"=>"some value"
(1 row)

postgres=# 
复制代码

 

按Key值查询:

复制代码
postgres=# SELECT * FROM hstore_test WHERE data ? 'key4';
 item_id |                           data                           
---------+----------------------------------------------------------
       1 | "key1"=>"value1", "key3"=>"value3", "key4"=>"some value"
(1 row)

postgres=# 
postgres=# SELECT * FROM hstore_test WHERE NOT data ? 'key5';
 item_id |                           data                           
---------+----------------------------------------------------------
       1 | "key1"=>"value1", "key3"=>"value3", "key4"=>"some value"
(1 row)

postgres=# SELECT * FROM hstore_test WHERE data @> '"key4"=>"some value"'::hstore;
 item_id |                           data                           
---------+----------------------------------------------------------
       1 | "key1"=>"value1", "key3"=>"value3", "key4"=>"some value"
(1 row)

postgres=# SELECT data -> 'key4' FROM hstore_test;
  ?column?  
------------
 some value
(1 row)

postgres=# SELECT item_id, (each(data)).* FROM hstore_test WHERE item_id = 2;
 item_id | key | value 
---------+-----+-------
(0 rows)

postgres=# SELECT item_id, (each(data)).* FROM hstore_test WHERE item_id = 1;
 item_id | key  |   value    
---------+------+------------
       1 | key1 | value1
       1 | key3 | value3
       1 | key4 | some value
(3 rows)

postgres=# 
复制代码








版权声明:本文首发在云栖社区,遵循云栖社区版权声明:本文内容由互联网用户自发贡献,版权归用户作者所有,云栖社区不为本文内容承担相关法律责任。云栖社区已升级为阿里云开发者社区。如果您发现本文中有涉嫌抄袭的内容,欢迎发送邮件至:developer2020@service.aliyun.com 进行举报,并提供相关证据,一经查实,阿里云开发者社区将协助删除涉嫌侵权内容。

分享:
数据库
使用钉钉扫一扫加入圈子
+ 订阅

分享数据库前沿,解构实战干货,推动数据库技术变革

其他文章