原文地址:http://psql.info/?p=41

现在满地都是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)
Tagged with:  

PostgreSQL COPY 导入/导出数据

On 2023/06/02, in PostgreSQL, by admin

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

Tagged with:  

PostgreSQL UUID 函数

On 2023/02/26, in PostgreSQL, by admin

测试环境: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

Tagged with:  

PostgreSQL RPM 安装笔记

On 2023/02/06, in PostgreSQL, by admin

测试环境: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
Tagged with:  

MySQL & PostgreSQL 小命令对比

On 2023/02/05, in MySQL, PostgreSQL, by admin

对比下一些两个数据库常用的操作。分别使用自带的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                        \?
Tagged with:  

PostgreSQL Partitioning 表分区

On 2009/12/22, in PostgreSQL, by admin

测试版本: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啦!

Tagged with:  

PostgreSQL tablespace 表空间

On 2009/12/22, in PostgreSQL, by admin

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);
Tagged with:  
iBlog by PageLines

WP SlimStat