现在满地都是KV数据库的文字，PostgreSQL 也有类似的结构。不过是通过其强大的扩展方式实现的。
官网文档请参考：http://www.postgresql.org/docs/current/static/hstore.html
本文测试环境在 Mac OS 下，Pg采用源码编译。
编译 hstore 扩展
mac:~ smallfish$ cd Downloads/postgresql-9.0.1/contrib/hstore/ mac:hstore smallfish$ make ... 一堆编译信息 mac:hstore smallfish$ sudo make install
导入到数据库中，注意必须以 postgres 用户，如果需要装入到指定数据库，请指明。这里采用默认数据库。
mac:hstore smallfish$ ls /opt/postgresql/share/contrib/ hstore.sql uninstall_hstore.sql mac:hstore smallfish$ psql -U postgres -f /opt/postgresql/share/contrib/hstore.sql ... 一堆导入命令
进入数据库，建一个测试表
postgres=# CREATE TABLE testhstore (id SERIAL, value hstore); NOTICE: CREATE TABLE will create implicit sequence "testhstore_id_seq" for serial column "testhstore.id" CREATE TABLE
查看下表结构
postgres=# \d
List of relations
Schema | Name | Type | Owner
--------+-------------------+----------+----------
public | testhstore | table | postgres
public | testhstore_id_seq | sequence | postgres
(2 rows)
postgres=# \d testhstore;
Table "public.testhstore"
Column | Type | Modifiers
--------+---------+---------------------------------------------------------
id | integer | not null default nextval('testhstore_id_seq'::regclass)
value | hstore |
尝试下简单 hstore类型
postgres=# select 'a=>1, b=>2'::hstore;
hstore
--------------------
"a"=>"1", "b"=>"2"
(1 row)
postgres=# select 'a=>1, b=>a'::hstore;
hstore
--------------------
"a"=>"1", "b"=>"a"
(1 row)
写几条测试数据先
postgres=# INSERT INTO testhstore (value) VALUES ('name=>smallfish, age=>29'::hstore);
INSERT 0 1
postgres=# SELECT * FROM testhstore;
id | value
----+----------------------------------
1 | "age"=>"29", "name"=>"smallfish"
(1 row)
postgres=# INSERT INTO testhstore (value) VALUES ('name=>nnfish, age=>20'::hstore);
INSERT 0 1
postgres=# INSERT INTO testhstore (value) VALUES ('name=>aaa, age=>30, addr=>China'::hstore);
INSERT 0 1
查看下所有数据
postgres=# SELECT * FROM testhstore; id | value ----+--------------------------------------------- 1 | "age"=>"29", "name"=>"smallfish" 2 | "age"=>"20", "name"=>"nnfish" 3 | "age"=>"30", "addr"=>"China", "name"=>"aaa" (3 rows)
查询列里面的指定 key
postgres=# SELECT id, value->'name' AS name FROM testhstore; id | name ----+----------- 1 | smallfish 2 | nnfish 3 | aaa (3 rows) postgres=# SELECT id, value->'name', value->'age' AS age FROM testhstore; id | ?column? | age ----+-----------+----- 1 | smallfish | 29 2 | nnfish | 20 3 | aaa | 30 (3 rows)
修改列某 key 值
postgres=# UPDATE testhstore SET value=value||('addr=>Shanghai') WHERE id = 2;
UPDATE 1
postgres=# SELECT * FROM testhstore;
id | value
----+---------------------------------------------------
1 | "age"=>"29", "name"=>"smallfish"
3 | "age"=>"30", "addr"=>"China", "name"=>"aaa"
2 | "age"=>"20", "addr"=>"Shanghai", "name"=>"nnfish"
(3 rows)
删除列里某 key
postgres=# UPDATE testhstore SET value=delete(value, 'addr') WHERE id = 3; UPDATE 1 postgres=# SELECT * FROM testhstore; id | value ----+--------------------------------------------------- 1 | "age"=>"29", "name"=>"smallfish" 2 | "age"=>"20", "addr"=>"Shanghai", "name"=>"nnfish" 3 | "age"=>"30", "name"=>"aaa" (3 rows)
按条件查询列里某 key ，注意要数据类型，CAST 强转。默认都是字符串的值。
postgres=# SELECT * FROM testhstore WHERE (value->'age')::INT > 20; id | value ----+---------------------------------- 1 | "age"=>"29", "name"=>"smallfish" 3 | "age"=>"30", "name"=>"aaa" (2 rows) postgres=# SELECT * FROM testhstore WHERE value->'name' = 'smallfish'; id | value ----+---------------------------------- 1 | "age"=>"29", "name"=>"smallfish" (1 row)
COPY 命令可以快速的导入数据到 PostgreSQL 中，文件格式类似CVS之类。适合批量导入数据，比 \i 和恢复数据表快。
导出表数据到文件或 STDOUT ：
COPY tablename [(column [, ...])]
TO {'filename' | STDOUT}
[[WITH]
[BINARY]
[OIDS]
[DELIMITER [AS] 'delimiter']
[NULL [AS] 'null string']
[CSV [HEADER]
[QUOTE [AS] 'quote']
[ESCAPE [AS] 'escape']
[FORCE NOT NULL column [, ...]]
导入文件或者 STDIN 到表中：
COPY tablename [(column [, ...])]
FROM {'filename' | STDIN}
[[WITH]
[BINARY]
[OIDS]
[DELIMITER [AS] 'delimiter']
[NULL [AS] 'null string']
[CSV [HEADER]
[QUOTE [AS] 'quote']
[ESCAPE [AS] 'escape']
[FORCE QUOTE column [, ...]]
导出表 employee 到默认输出 STDOUT：
psql> COPY employee TO STDOUT; 1 JG100011 Jason Gilmore [email protected] 2 RT435234 Robert Treat [email protected] 3 GS998909 Greg Sabino Mullane [email protected] 4 MW777983 Matt Wade [email protected]
导出表 employee 到 sql 文件：
psql> COPY employee TO '/home/smallfish/employee.sql';
从文件导入数据：
psql> COPY employeenew FROM '/home/smallfish/employee.sql';
psql> SELECT * FROM employeenew;
employeeid | employeecode | name | email
------------+--------------+---------------------+---------------
1 | JG100011 | Jason Gilmore | [email protected]
2 | RT435234 | Robert Treat | [email protected]
3 | GS998909 | Greg Sabino Mullane | [email protected]
4 | MW777983 | Matt Wade | [email protected]
(4 rows)
输出对象ID（OIDS）：
psql> COPY employee TO STDOUT OIDS; 24627 1 GM100011 Jason Gilmore [email protected] 24628 2 RT435234 Robert Treat [email protected] 24629 3 GS998909 Greg Sabino Mullane [email protected] 24630 4 MW777983 Matt Wade [email protected]
指定导出间隔符，默认是 \t ，这里为 | ：
psql>COPY employee TO STDOUT DELIMITER '|'; 1|GM100011|Jason Gilmore|[email protected] 2|RT435234|Robert Treat|[email protected] 3|GS998909|Greg Sabino Mullane|[email protected] 4|MW777983|Matt Wade|[email protected]
导入文件数据，指定间隔符为 | ：
psql> COPY employeenew FROM '/home/smallfish/employee.sql' DELIMITER |;
导出指定字段的数据：
psql> COPY employee (name,email) TO STDOUT; Jason Gilmore [email protected] Robert Treat [email protected] Greg Sabino Mullane [email protected] Matt Wade [email protected]
为 NULL 字段设置默认值：
psql> COPY employee TO STDOUT NULL 'no email'; Jason Gilmore no email Robert Treat [email protected] Greg Sabino Mullane [email protected] Matt Wade no email
导出为CVS格式：
psql> COPY employee (name, email) TO '/home/smallfish/employee.csv' CSV HEADER;
参考资料：Apress Beginning PHP and PostgreSQL 8: From Novice to Professional
测试环境：PostgreSQL 8.4
默认PostgreSQL是木有UUID函数可使用，而不像MySQL提供uuid()函数，不过在contrib里有，只需要导入一下uuid-ossp.sql即可。（PS：注意权限问题，要Pg可读改文件。）
导入很简单，下面是win下面测试，其他平台类似该操作：
D:\>psql -U postgres -h localhost -f D:\PostgreSQL\8.4\share\contrib\uuid-ossp.sql Password for user postgres: SET CREATE FUNCTION CREATE FUNCTION CREATE FUNCTION CREATE FUNCTION CREATE FUNCTION CREATE FUNCTION CREATE FUNCTION CREATE FUNCTION CREATE FUNCTION CREATE FUNCTION
进入psql，执行：
postgres=# select uuid_generate_v1();
uuid_generate_v1
--------------------------------------
86811bd4-22a5-11df-b00e-ebd863f5f8a7
(1 row)
postgres=# select uuid_generate_v4();
uuid_generate_v4
--------------------------------------
5edbfcbb-1df8-48fa-853f-7917e4e346db
(1 row)
主要就是uuid_generate_v1和uuid_generate_v4，当然还有uuid_generate_v3和uuid_generate_v5。其他使用可以参见PostgreSQL官方文档 uuid-ossp。
测试环境：REHL 5.3
PostgreSQL版本：8.4.2
1. 首先检查下是否已经有PostgreSQL安装程序(俺的机器有pg-libs 8.1，无视之)
shell> rpm -qa | grep postgres
2. 下载最新的8.4.2RPM安装包，这个FTP速度挺快的。:)
shell> wget http://ftp.easynet.be/postgresql/binary/v8.4.2/linux/rpms/redhat/rhel-5-x86_64/postgresql-server-8.4.2-1PGDG.rhel5.x86_64.rpm shell> wget http://ftp.easynet.be/postgresql/binary/v8.4.2/linux/rpms/redhat/rhel-5-x86_64/postgresql-contrib-8.4.2-1PGDG.rhel5.x86_64.rpm shell> wget http://ftp.easynet.be/postgresql/binary/v8.4.2/linux/rpms/redhat/rhel-5-x86_64/postgresql-libs-8.4.2-1PGDG.rhel5.x86_64.rpm shell> wget http://ftp.easynet.be/postgresql/binary/v8.4.2/linux/rpms/redhat/rhel-5-x86_64/postgresql-devel-8.4.2-1PGDG.rhel5.x86_64.rpm shell> wget http://ftp.easynet.be/postgresql/binary/v8.4.2/linux/rpms/redhat/rhel-5-x86_64/postgresql-8.4.2-1PGDG.rhel5.x86_64.rpm shell> wget http://ftp.easynet.be/postgresql/binary/v8.4.2/linux/rpms/redhat/rhel-5-x86_64/postgresql-plpython-8.4.2-1PGDG.rhel5.x86_64.rpm
3. 安装PostgreSQL(要注意下顺序)，首先需要更新pg-libs版本。
后面几个不需要的话可以不装。主要是一些扩展功能。
shell> rpm -ivh postgresql-libs-8.4.2-1PGDG.rhel5.x86_64.rpm shell> rpm -ivh postgresql-8.4.2-1PGDG.rhel5.x86_64.rpm shell> rpm -ivh postgresql-server-8.4.2-1PGDG.rhel5.x86_64.rpm shell> rpm -ivh postgresql-contrib-8.4.2-1PGDG.rhel5.x86_64.rpm shell> rpm -ivh postgresql-devel-8.4.2-1PGDG.rhel5.x86_64.rpm shell> rpm -ivh postgresql-plpython-8.4.2-1PGDG.rhel5.x86_64.rpm
4. RPM安装完后，需要初始化PostgreSQL库。service初次启动会提示。
如果是源码安装这个过程就是对应的initdb -D，指定data目录。RPM默认对应目录是/var/lib/pgsql/data。
shell> service postgresql initdb
5. service启动PostgreSQL
shell> service postgresql start
到上面这一步基本是安装完成了。下面的是修改数据库用户密码和登陆相关。
6. 切换到postgres用户，修改数据库密码。(注意系统用户和数据库用户密码是两个概念，虽然名字都叫postgres)
修改完需要重启数据库，这里咱不重启，等修改完认证配置再一起重启。
shell> su - postgres shell> psql postgres=# ALTER USER postgres WITH PASSWORD '123456'; postgres=# \q
7. 修改认证文件/var/lib/pgsql/data/pg_hba.conf，登陆使用密码。md5格式。
shell> vi /var/lib/pgsql/data/pg_hba.conf 修改ident为md5 (local, host)
8. service重启PostgreSQL
shell> service postgresql restart
9. 再次进入测试，应该会提示输入密码鸟 
shell>psql -U postgres
对比下一些两个数据库常用的操作。分别使用自带的client程序。
MySQL命令行：
mysql -u 用户名 -h 主机地址 -P 端口号 数据库名 -p
PostgreSQL命令行：
psql -U 用户名 -h 主机地址 -p 端口号 数据库名
操作对比：
mysql psql SHOW DATABASES; \l USE db-name; \c db-name SHOW TABLES; \d SHOW USERS; \du SHOW COLUMNS; \d table-name SHOW PROCESSLIST; SELECT * FROM pg_stat_activity; SELECT now()\G \x 可以打开和关闭类似\G功能 SOURCE /path.sql \i /path.sql LOAD DATA INFILE ... \copy ... \h \?
测试版本：pg 8.3 (ubuntu)
在pg里表分区是通过表继承来实现的，一般都是建立一个主表，里面是空，然后每个分区都去继承它。
创建表分区步骤如下：
1. 创建主表
CREATE TABLE users ( uid int not null primary key, name varchar(20));
2. 创建分区表(必须继承上面的主表)
CREATE TABLE users_0 ( check (uid >= 0 and uid< 100) ) INHERITS (users); CREATE TABLE users_1 ( check (uid >= 100)) INHERITS (users);
3. 在分区表上建立索引，其实这步可以省略的哦
CREATE INDEX users_0_uidindex on users_0(uid); CREATE INDEX users_1_uidindex on users_1(uid);
4. 创建规则RULE
CREATE RULE users_insert_0 AS
ON INSERT TO users WHERE
(uid >= 0 and uid < 100)
DO INSTEAD
INSERT INTO users_0 VALUES (NEW.uid,NEW.name);
CREATE RULE users_insert_1 AS
ON INSERT TO users WHERE
(uid >= 100)
DO INSTEAD
INSERT INTO users_1 VALUES (NEW.uid,NEW.name);
下面就可以测试写入数据啦：
postgres=# INSERT INTO users VALUES (100,'smallfish'); INSERT 0 0 postgres=# INSERT INTO users VALUES (20,'aaaaa'); INSERT 0 0 postgres=# select * from users; uid | name -----+----------- 20 | aaaaa 100 | smallfish (2 笔资料列) postgres=# select * from users_0; uid | name -----+------- 20 | aaaaa (1 笔资料列) postgres=# select * from users_1; uid | name -----+----------- 100 | smallfish (1 笔资料列)
到这里表分区已经可以算完了，不过还有个地方需要修改下，先看count查询把。
postgres=# EXPLAIN SELECT count(*) FROM users where uid<100;
QUERY PLAN
------------------------------------------------
Aggregate (cost=62.75..62.76 rows=1 width=0)
-> Append (cost=6.52..60.55 rows=879 width=0)
-> Bitmap Heap Scan on users (cost=6.52..20.18 rows=293 width=0)
Recheck Cond: (uid < 100)
-> Bitmap Index Scan on users_pkey (cost=0.00..6.45 rows=293 width=0)
Index Cond: (uid < 100)
-> Bitmap Heap Scan on users_0 users (cost=6.52..20.18 rows=293 width=0)
Recheck Cond: (uid < 100)
-> Bitmap Index Scan on users_0_uidindex (cost=0.00..6.45 rows=293 width=0)
Index Cond: (uid < 100)
-> Bitmap Heap Scan on users_1 users (cost=6.52..20.18 rows=293 width=0)
Recheck Cond: (uid < 100)
-> Bitmap Index Scan on users_1_uidindex (cost=0.00..6.45 rows=293 width=0)
Index Cond: (uid < 100)
(14 笔资料列)
按照本来想法，uid小于100，理论上应该只是查询users_0表，通过EXPLAIN可以看到其他他扫描了所有分区的表。
postgres=# SET constraint_exclusion = on; SET
postgres=# EXPLAIN SELECT count(*) FROM users where uid<100;
QUERY PLAN
------------------------------------------------
Aggregate (cost=41.83..41.84 rows=1 width=0)
-> Append (cost=6.52..40.37 rows=586 width=0)
-> Bitmap Heap Scan on users (cost=6.52..20.18 rows=293 width=0)
Recheck Cond: (uid < 100)
-> Bitmap Index Scan on users_pkey (cost=0.00..6.45 rows=293 width=0)
Index Cond: (uid < 100)
-> Bitmap Heap Scan on users_0 users (cost=6.52..20.18 rows=293 width=0)
Recheck Cond: (uid < 100)
-> Bitmap Index Scan on users_0_uidindex (cost=0.00..6.45 rows=293 width=0)
Index Cond: (uid < 100)
(10 笔资料列)
到这里整个过程都OK啦！
pgsql允许管理员在文件系统里定义表空间存储位置，这样创建数据库对象时候就可以引用这个表空间了。好处就不用多说了，可以把数据库对象存储到不同的分区上，比如更好的存储之类。默认initdb之后会有两个表空间pg_global和pg_default。
查看pgsql当前表空间有哪些可以试试下面：
postgres=> SELECT spcname FROM pg_tablespace; spcname ------------ pg_default pg_global (2 rows)
或：
postgres=> \db
Name | Owner | Location
------------+----------+----------
pg_default | postgres |
pg_global | postgres |
建立表空间需要注意的主要的是权限问题，而且要在新的空目录上建立，权限属于数据库管理员比如默认postgres。
1. 建立目录
$ mkdir /home/smallfish/pgdata $ sudo chown -R postgres:postgres /home/smallfish/pgdata
2. 进入psql
$ psql -U postgres -h 192.168.0.122
如果权限没设置好下面语句会报错
postgres=> CREATE TABLESPACE space1 LOCATION '/home/smallfish/pgdata';
建测试表
postgres=> CREATE TABLE foo(i int) TABLESPACE space1;
可以查看表空间目录下多了文件
postgres=> \! ls /home/smallfish/pgdata
删除表空间，需要注意的是先要删除所有该表空间里的对象
postgres=> DROP TABLESPACE space1;
ok，到这里已经建立好表空间了。当然每次建表都指定TABLESPACE也有点麻烦，来点默认的把。
postgres=> SET default_tablespace = space1; postgres=> CREATE TABLE foo(i int);
