PostgreSQL 学习笔记

简介

PostgreSQL 是世界上功能最强大的关系型数据库，除了标准的 SQL 的支持外，还有丰富的特性，可以通过扩展来增强数据库的能力。本文是我学习 PostgreSQL 过程中的一些总结。

安装

macOS

1
2


brew update
brew install postgresql

常用命令

\? 显示帮助信息
\l 列出所有数据库，相当于 show databases
\c <database_name> 连接指定的数据库，相当于 use <database_name>
\dt 列出数据库中所有的表，相当于 show tables
\ds 列出数据库中所有序列
\d <table_name> 显示该表的详细信息
\password <username> 修改指定用户的密码
\dx 列出数据库已使用的扩展
\x 开启/关闭展开显示，当 select 的输出结果太长时，可以试试这个模式

扩展

我们知道，PostgreSQL 有非常丰富的扩展供我们使用，下面列举一些常见的扩展：

uuid-ossp

该模块提供了一些函数来生成 UUID。如 uuid_generate_v1, uuid_generate_v4 等。

1
2
3
4
5


CREATE EXTENSION "uuid-ossp";
SELECT uuid_generate_v4();
           uuid_generate_v4
--------------------------------------
 cc230056-ed05-4bcf-a12f-1611728ac199

从官网上了解到，如果我们只需要生成 UUID (v4)，那么可以考虑使用 pgcrypto 模块。

pgcrypto

该模块提供了一些加密的函数，其中 gen_random_uuid 这个函数可以用来生成 UUID。

1
2
3
4
5


CREATE EXTENSION "pgcrypto";
SELECT gen_random_uuid();
           gen_random_uuid
--------------------------------------
 f8ea5410-cac7-4811-bfbd-5e0b8d0a0cc4

hstore

该模块为 PostgreSQL 提供存储 Key/Value 数据的功能，你可以定义 hstore 数据列来存储这样的数据，并可对这些数据进行分组、排序和唯一检索的查询。

1
2
3
4
5


CREATE EXTENSION "hstore";
SELECT 'a=>1, b=>2'::hstore;
       hstore
--------------------
 "a"=>"1", "b"=>"2"

对于 hstore 的增删查改操作，我们可以用如下的操作：

hstore -> text 获取 hstore 中指定的 key。
hstore || hstore 合并两个 hstore，常用于更新操作。

delete(hstore, text) 删除 hstore 中指定的 key。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17


CREATE TABLE testhstore (id SERIAL, value hstore);
INSERT INTO testhstore (value) VALUES ('name=>smallfish, age=>29'::hstore);
INSERT INTO testhstore (value) VALUES ('name=>nnfish, age=>20'::hstore);
INSERT INTO testhstore (value) VALUES ('name=>aaa, age=>30, addr=>China'::hstore);

-- 查询 key
SELECT id, value->'name' AS name FROM testhstore;
id |   name
----+-----------
1 | smallfish
2 | nnfish
3 | aaa
(3 rows)
-- 更新 key
UPDATE testhstore SET value = value || ('addr=>Shanghai') WHERE id = 2;
-- 删除 key
UPDATE testhstore SET value = delete(value, 'addr') WHERE id = 3;

PostGIS

PostGIS 在对象关系型数据库PostgreSQL上增加了存储管理空间数据的能力，相当于Oracle的spatial部分。PostGIS最大的特点是符合并且实现了OpenGIS的一些规范，是最著名的开源GIS数据库。

PostPic

PostPic 用来在数据库内进行图像处理，PostPic 为 SQL 增加了 image 类型，还包含很多相关的函数用来处理图片以及从图片中抽取对应的属性。

JSON / JSONB

JSON 类型是在 PostgreSQL 9.2 版本中加入的，而 JSONB 则是在 9.4 版本中加入的。两者的区别在于：JSON 是纯文本，而 JSONB 则是二进制。由于 JSON 存储的是未经处理过的 JSON 字符串，不需要进行解析，而 JSONB 插入时需要进行解析，因此在插入速度上 JSON 会比 JSONB 快，但在查询时，JSONB 会比 JSON 快。

因此，如果我们的应用需要频繁操作 JSON 的话，用 JSONB 会比较合适；相反，如果只是需要存储的话，用 JSON 会比较合适。

下面是一些常用的 JSON / JSONB 的操作：

json -> int 根据指定索引获取 JSON 数组中的元素
json -> text 获取 JSON 中指定字段的值
json #> array 获取 JSON 中指定路径的值

需要注意的是，上面三种操作都是返回 JSON(B) 类型的结果，有些情况下我们需要把结果转换成字符串之后才能进行比较。这时候就需要在上面几个操作符改成 ->>, #>>，这样返回的结果就是 text 类型，可以直接进行比较了。

1
2
3
4
5
6
7


SELECT
  pg_typeof('{ "name": "john", "age": 30, "addr": "shanghai" }'::json -> 'age'),
  pg_typeof('{ "name": "john", "age": 30, "addr": "shanghai" }'::json ->> 'age');

 pg_typeof | pg_typeof
-----------+-----------
 json      | text

看看下面的例子：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18


CREATE TABLE testjson (id SERIAL, value json);
INSERT INTO testjson (value) values ('{ "name": "john", "age": 30, "addr": "shanghai" }');
INSERT INTO testjson (value) values ('{ "name": "lucy", "age": 20, "addr": "guangzhou" }');
INSERT INTO testjson (value) values ('{ "name": "tim", "age": 24, "addr": "beijing", "books": [1,2,3,4,5] }');

-- 根据指定 key 查询
SELECT * FROM testjson WHERE value ->> 'age' > '20';

 id |                                 value
----+-----------------------------------------------------------------------
  1 | { "name": "john", "age": 30, "addr": "shanghai" }
  3 | { "name": "tim", "age": 24, "addr": "beijing", "books": [1,2,3,4,5] }

-- 根据指定路径查询
SELECT * FROM testjson WHERE value #>> '{books, 3}' = '4';
 id |                                 value
----+-----------------------------------------------------------------------
  3 | { "name": "tim", "age": 24, "addr": "beijing", "books": [1,2,3,4,5] }

由于 JSON 是纯文本，因此对其进行更新操作只是相当于操作字符串，如：

1

UPDATE testjson SET value = replace(value::text, '"john"', '"mark"')::json WHERE id = 1;

这种方式相当不友好，因此，如果需要经常操作 JSON 的话，建议使用 JSONB 类型，它提供了多种操作方式。

1
2
3
4
5
6
7


-- for JSONB
-- 根据指定字段更新
UPDATE testjsonb SET value = jsonb_set(value, '{name}', 'mark') WHERE id = 1;
-- 根据指定路径更新
UPDATE testjsonb SET value = jsonb_set(value, '{books, 3}', '10') WHERE id = 3;
-- 移除 age 字段
UPDATE testjsonb SET value = value - 'age' WHERE id = 2;

升级

macOS

停止 PostgreSQL 服务器
1

brew services stop postgresql

升级 PostgreSQL

1

brew update && brew upgrade postgresql

为新版 PostgreSQL 创建数据库

1

initdb /usr/local/var/postgres9.6.2 -E utf8

迁移数据

1
2
3
4
5
6


pg_upgrade \
  -d /usr/local/var/postgres \
  -D /usr/local/var/postgres9.6.2 \
  -b /usr/local/Cellar/postgresql/9.5/bin/ \
  -B /usr/local/Cellar/postgresql/9.6.2/bin/ \
  -v

把新数据库移动到 PostgreSQL 默认的位置

1
2


mv /usr/local/var/postgres /usr/local/var/postgres9.5
mv /usr/local/var/postgres9.6.2 /usr/local/var/postgres

重启 PostgreSQL 服务器
1

brew services start postgresql

常见坑

单引号与双引号

单引号 (') 和双引号 (") 在 PostgreSQL 中有明确的区分，单引号用来引用值 (value)，而双引号用来引用标识符，如字段名，表名等等。因此，下面两段 SQL 会有不同的结果：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11


-- 单引号
SELECT id FROM posts WHERE id = 'ad3eef9a-e081-46a9-9304-4a1d1637e542';
                  id
--------------------------------------
 ad3eef9a-e081-46a9-9304-4a1d1637e542
(1 row)

-- 双引号
SELECT id FROM posts WHERE id = "ad3eef9a-e081-46a9-9304-4a1d1637e542";
ERROR:  column "ad3eef9a-e081-46a9-9304-4a1d1637e542" does not exist
LINE 1: select id from posts where id = "ad3eef9a-e081-46a9-9304-4a1...

参考资料

http://www.postgres.cn/docs/9.4/index.html http://postgresguide.com/ http://www.ruanyifeng.com/blog/2013/12/getting_started_with_postgresql.html https://hashrocket.com/blog/posts/faster-json-generation-with-postgresql https://www.postgresql.org/docs/9.4/static/uuid-ossp.html https://www.postgresql.org/docs/9.4/static/pgcrypto.html http://www.postgresqltutorial.com/postgresql-hstore/ https://www.postgresql.org/docs/9.5/static/sql-do.html https://keita.blog/2016/01/09/homebrew-and-postgresql-9-5/ http://www.oschina.net/news/28211/postgresql-most-useful-extensions http://chenxiaoyu.org/2011/02/19/postgresql-key-value-hstore.html https://segmentfault.com/a/1190000002911580 https://www.citusdata.com/blog/2016/07/14/choosing-nosql-hstore-json-jsonb/ http://blog.lerner.co.il/quoting-postgresql/ https://wiki.postgresql.org/wiki/Things_to_find_out_about_when_moving_from_MySQL_to_PostgreSQL http://stackoverflow.com/questions/38777535/what-is-the-difference-between-and-in-postgres-sql

文章目录

简介

安装

macOS

常用命令

扩展

uuid-ossp

pgcrypto

hstore

PostGIS

PostPic

JSON / JSONB

升级

macOS

常见坑

单引号与双引号

参考资料