【PostgreSQL】PostgreSQL hstore类型数据增删改查

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使用