1、登陆数据库
psql -U postgres -d postgres
2、创建数据库hstore,并安装extension
create database hstore;
create extension hstore;
3、建表
create table users(
id serial,
info hstore
);
4、插入数据
insert into users values(1,'"name"=>"zhangsan","age"=>"20"');
5、查询数据
select info from users;
6、增加数据
update users set info = info || '"company"=>"Qunar"' :: hstore;
7、删除数据
update users set info = delete(info, 'company');
8、将key值转为set输出。
select skeys(info) from users;
9、将key值转为array输出。
select akeys(info) from users;
10、将value值转为set输出。
select svals(info) from users;
11、将value值转为array输出。
select avals(info) from users;
12、将hstore值转为array输出。
select avals(info) from users;
13、查询age为20的所有记录。
查询之前再入两条数据
insert into users values(1,'"name"=>"lisi","age"=>"20"');
insert into users values(1,'"name"=>"wangwu","age"=>"21"');
查询
select * from users where info@'age=>20';
文档参考:http://www.postgresql.org/docs/current/static/hstore.html
PostgreSQL HSTORE类型
hstore 模块实现了将键/值对
存储到单个值的 HSTORE
数据类型。注意,HSTORE
中的 键
和 值
都只能是字符串。
启用 hstore 模块
使用 HSTORE
数据类型之前,需要先启用 hstore 模块:
CREATE EXTENSION hstore;
创建包含 HSTORE
数据类型的表
CREATE TABLE books ( id serial primary key, title VARCHAR (255), attr hstore );
插入数据
INSERT INTO books (title, attr) VALUES ( 'PostgreSQL 轻松学', '"paperback" => "685", "publisher" => "sjk66.com", "language" => "简体中文", "ISBN-13" => "1234567890123", "weight" => "412 克"' );
查询指定键的数据
SELECT attr -> 'ISBN-13' AS isbn FROM books;
在 WHERE 中使用键
SELECT attr -> 'weight' AS weight FROM books WHERE attr -> 'ISBN-13' = '1234567890123'
给已存在的数据添加键值对
UPDATE books SET attr = attr || '"freeshipping"=>"yes"' :: hstore;
更新键值对
UPDATE books SET attr = attr || '"freeshipping"=>"no"' :: hstore;
删除键值对
UPDATE books SET attr = delete(attr, 'freeshipping');
判断 HSTORE
字段是否包含某个键
SELECT title, attr->'publisher' as publisher, attr FROM books WHERE attr ? 'publisher';
判断是否含有某键值对
SELECT title FROM books WHERE attr @> '"weight"=>"412 克"' :: hstore;
判断是否包含多个键
SELECT title FROM books WHERE attr ?& ARRAY [ 'language', 'weight' ];
获取所有键
SELECT akeys (attr) FROM books;
或
SELECT skeys (attr) FROM books;
获取所有值
SELECT avals (attr) FROM books;
或
SELECT svals (attr) FROM books;
转换成 JSON
SELECT title, hstore_to_json (attr) json FROM books;
转换成记录集
SELECT title, (EACH(attr) ).* FROM books;
不知道能不能当hbase使用