PostgreSQL 9.5.1官方文档学习笔记


本文针对目前最新版PostgreSQL 9.5.1，若非说明，文中所说文档即指官方文档。本人刚接触PostgreSQL不久，文中不免错漏，请大家指正；随着了解深入，本文[可能]会不定期更新补足。

JSON

PostgreSQL支持Json格式数据，有两种类型：json和jsonb。两者在效率上有所区别，而这是因为jsonb存储的是格式化后的二进制数据，所以在写入时，json类型比较快，而在检索时（注意这里说的检索不是简单的读取整个数据，而是比如检索json数据中某个键的值的场景），jsonb效率较高。一般情况下，使用jsonb就可以了。json数据是为了弥补关系型数据在伸缩性扩展性上的不足，但是文档也说了，不能啥都往里放，要考虑数据原子性和数据大小。

json类型可以作包含判断和是否存在的判断（containment or existence），表示符号分别为@>和?（以及其它一些变种）。对于这两种牵涉到多个键和元素的判断场景，json类型比下面要讲的arrays更适合，因为其对查询有内在的优化机制，而array只是单纯的线性查找。

若json列需要经常检索，那么可以在其上建立GIN索引，jsonb支持两种特有的GIN索引jsonb_ops和jsonb_path_ops。创建的语法如下：

CREATE INDEX idxgin ON api USING GIN (jdoc);
CREATE INDEX idxginp ON api USING GIN (jdoc jsonb_path_ops); -- 只是比前一行多了jsonb_path_ops标记

The jsonb_path_ops supports indexing the @> operator only. 关于这两者使用和技术实现上的区别可参看：PgSQL 9.4 新特性jsonb类型解析，PostgreSQL 9.4 中使用 jsonb

我们可以对json数据中的某一属性建GIN索引（可称之为属性索引），如：CREATE INDEX idxgintags ON api USING GIN ((jdoc -> ’tags’));  这能提升检索键值对的效率，比如如下场景：

SELECT jdoc->’guid’, jdoc->’name’ FROM api WHERE jdoc -> ’tags’ ? ’qui’;

当然我们也可以不使用属性索引，而是换一种查询方式：

SELECT jdoc->’guid’, jdoc->’name’ FROM api WHERE jdoc @> ’{"tags": ["qui"]}’;

jsonb also supports btree and hash indexes. These are usually useful only if it’s important to check equality of complete JSON documents.

Array

PostgreSQL支持Array类型，其字段声明有如下几种方式：

1 CREATE TABLE emptable (
2  　　arraycol1 integer[],
3  　　arraycol2 text[][],
4  　　arraycol3 text[3],
5  　　arraycol4 integer ARRAY,
6  　　arraycol5 integer ARRAY[4]
7 );

在列声明时我们可以指定数组中的元素类型、维度和长度，后两者然并卵，当前版本的PostgreSQL会忽略这两者的设置，它们更多是以一种备注的意义存在。

插入格式如下：

INSERT INTO emptable VALUES (
    ’{10000, 10000, 10000, 10000}’, 
    ’{{"meeting", "lunch"}, {"training", "presentation"}}’,
　　ARRAY[10000, 10000, 10000, 10000], 
　　ARRAY[[’meeting’, ’lunch’], [’training’, ’presentation’]]
);

注意字符串的写法，第3行单引号内部是以双引号包含，第5行ARRAY构造函数方式则是以单引号包含。多维数组中每个元素的长度要一致，否则会报错，比如不能

INSERT INTO emptable VALUES (
    ’{10000, 10000, 10000, 10000}’, 
    ’{{"meeting", "lunch"}, {"training"}}’ -- error
);

访问，arraycol[n]，PostgreSQL的数组默认下标是1基的，这点需要注意，即默认情况下我们访问数组第项应使用arraycol[1]，而非惯常的arraycol[0]，当然我们可以 SET arraycol[-2:7] = '{XXOO,...}'的方式设置数组的上下界（这个例子就变成了-2基）；多维数组访问，以二维数组为例，arraycol[n][m]；若下标超出数组长度则返回null，并不会抛出异常。若访问数组某部分毗邻元素，则需要用到slice形式，形如arraycol[1:3][2:5]，表示要访问1到3项，并且取这三项中的2到5项——仍以数组形式——返回，第一个中括号表示第1维，第二个表示第2维，以此类推。需要注意的是arraycol[1:3][2]，并不是表示取1到3项中的第2项，PostgreSQL认为只要有一个维度是slice形式，则所有你要访问的维度都是slice形式，若只有1位数，则前面附加1:，即arraycol[1:3][2] == arraycol[1:3][1:2]。如果slice的下标超出数组长度，又会怎样呢？有两种情况：若起始下标就超出了，那么返回空数组（文档中说是因为历史原因）；若只是结束下标超出，则返回从起始下标到数组末尾这段数据。

一些函数：array_dims，以文本形式返回数组的所有维度；array_length，指定维度的数组长度；array_upper，返回指定维度上界；array_lower，返回指定维度下界；cardinality，所有维度的元素个数总和（不知能否用于子数组或子维度）。

对于一维数组，set arraycol[m] = xxoo，若m大于当前长度，那么arraycol将自动扩充到m上界，而原上界到新上界之间位置的项将置为null，重复一遍，目前只有一维数组有这个特性。

array_prepend、array_append、array_cat用于元素的头尾插入或数组的连接，前两者只能用于一维数组，一般我们可以使用连接符 || 来提供这三者的功能。

数组检索相关：any、all、generate_subscripts、array_position、array_positions、&&（左操作数是否包含右操作数）。关于数组检索，官方文档有这么段提示：数组不是集合，搜索数组中的特定元素通常表明你的数据库设计有问题。 数组字段通常是可以分裂成独立的表（with a row for each item of the array）。 很明显表要容易搜索得多，并且在元素数目非常庞大的时候也可以更好地伸展。这似乎表示数组是设计用来进行直接展示的，若业务查询需要关联数组中的特定值，则需要考虑重新设计或使用其它类型。

插：在使用MySql的时候，我们通常会被告知，使用有最大长度的char或者varchar会在性能方面有好处，而在PostgreSQL中，却不一定是这样。在PostgreSQL中，这三种类型的字符串数据并没有明显的性能差别，而且character(n)类型的数据一般是最慢的，因为固定长度导致更多的存储空间。所以，一般来说，text或者character varying就行了。

Functions

PostgreSQL没有存储过程的概念（博主也不明白为何其它数据库要划分存储过程和函数）。函数会返回最后一条语句的结果[的第一行数据]；若要返回结果集，需要显示声明要返回某类型的结果集或Table。 Unless the function is declared to return void, the last statement must be a SELECT, or an INSERT, UPDATE, or DELETE that has a RETURNING clause. You cannot use transaction control commands, e.g. COMMIT, SAVEPOINT, and some utility commands, e.g. VACUUM, in SQL functions. 函数体以双"$"符号或单引号包裹，若用单引号包裹则需要注意特殊字符转义。可以在函数体内以参数名（9.2及以后版本支持）或"$n"的方式引用参数。举个例子：

CREATE FUNCTION tf1 (accountno integer, debit numeric) RETURNS integer AS $$ 
    UPDATE bank 
        SET balance = balance - debit 
        WHERE accountno = tf1.accountno -- 由于参数名和列同名，前面需要加函数名作为前缀
    -- ;SELECT balance FROM bank WHERE accountno = tf1.accountno; 
    RETURNING balance; 
$$ LANGUAGE SQL;

在INSERT INTO或者UPDATE的时候在最后面加上RETURNING colname，PostgreSQL会在插入或者更新数据之后会返回你指定的字段。

函数可以接收、返回多个字段，将这多个字段看作一个整体，称为复合类型。比如数据表中的一行，或者使用ROW构造函数构造的一行数据，或者以逗号分隔的多个字段。我们可以显式定义自己的复合类型，如：

CREATE TYPE inventory_item AS (
    name            text,
    supplier_id     integer,
    price           numeric
);

然后就可以将inventory_item用于很多地方了，甚至将一个表字段类型设置为inventory_item，如下：

CREATE TABLE on_hand (
    item      inventory_item,
    count     integer
);

INSERT INTO on_hand VALUES (ROW('fuzzy dice', 42, 1.99), 1000);

在你创建表的时候，也会自动创建一个复合类型，名字与表名字相同，表示该表的复合类型。需要注意的是，表定义的各项约束（如不可为空）对自动创建的同名复合类型无效。

关于复合类型值写法，上面的ROW方式比较常用，如果是多个字段，那么ROW可以省略，即('fuzzy dice', 42, 1.99)；还可以一般格式——'("fuzzy dice",42,1.99)'——外层以单引号包裹。我们可以操作复合类型的整体，也可以针对其某几个字段操作，具体请参考文档。

回到函数的介绍，比如下面两段代码表示的是同一个意思：

-- 1
CREATE FUNCTION new_emp() RETURNS emp AS $$ 
    SELECT 
        text ’None’ AS name,  -- 注意类型转换
        1000.0 AS salary, 
        25 AS age, 
        point ’(2,2)’ AS cubicle; 
$$ LANGUAGE SQL;    -- 字段顺序和类型要和返回类型（此处是emp）保持一致

-- 2
CREATE FUNCTION new_emp() RETURNS emp AS $$ 
    SELECT ROW(’None’, 1000.0, 25, ’(2,2)’)::emp; 
$$ LANGUAGE SQL;

双冒号:: 表示类型转换。

前面说到，函数能返回集合和表，返回表是最近出版的SQL标准之一，所以可能比返回集合更好一点；但是对于返回表来说，It is not allowed to use explicit OUT or INOUT parameters with the RETURNS TABLE notation — you must put all the output columns in the TABLE list.

PostgreSQL安装与配置

不得不说，自从入门了Linux之后，技术接触面广了很多，更乐意尝试.NET“标配”之外的东西。要在CentOS7.0上安装PostgreSQL，先到PostgreSQL RPM Building Project - Repository Packages找到对应的RPM包，并用yum安装：

yum install https://download.postgresql.org/pub/repos/yum/9.5/RedHat/rhel-7-x86_64/pgdg-centos95-9.5-2.noarch.rpm

上步只是install了RPM包，then，安装postgresql-server（还有其它一些packages，不过我们暂时安装postgresql-server即可）

yum install postgresql95-server

如果你安装的是9.4的版本，只要把上面的数字95改成94即可。

Due to policies for Red Hat family distributions, the PostgreSQL installation will not be enabled for automatic start or have the database initialized automatically. To make your database installation complete, you need to perform these two steps:

/usr/pgsql-9.5/bin/postgresql95-setup initdb    #初始化库
systemctl start postgresql-9.5.service    #启动

为了其它主机能连接到服务器，需要进行一些配置。关于系统参数配置，PostgreSQL提供了多种方式，适用场景稍有不同。这里选择编辑postgresql.conf文件的方式，另外还有个postgresql.auto.conf文件，保存的是系统参数默认值，是不允许直接编辑的，可以使用ALTER SYSTEM 命令进行配置值设定。postgresql.conf存储在PostgreSQL的data目录下，data目录可以在initdb时指定，如下：

initdb -D /usr/local/pgsql/data
#或者如下
pg_ctl -D /usr/local/pgsql/data initdb

This may be more intuitive if you are using pg_ctl for starting and stopping the server, so that pg_ctl would be the sole command you use for managing the database server instance.
不指定data目录的话，会默认给你一个，博主这用find命令看到是/var/lib/pgsql/9.5/data/。因为PostgreSQL实例是依赖于data目录的，所以可以在一台机子上开多个实例，每个实例都有自己的data目录，配置自然也不同；要pg_ctl启动、停止、重启等操作时需要带上data目录，或者指定PGDATA环境变量，否则不知道针对哪个实例进行操作。

find / -name postgresql.conf
#输出 /var/lib/pgsql/9.5/data/postgresql.conf

找到之后，就可以进行设置了，顺便熟悉下vi的操作。

1 vi postgresql.conf     #打开，此时为一般模式
2 /address     #定位到listen_addresses
3 0     #或者home键，移动光标到该行最前面
4 X     #删除最前面的井号，即取消该行注释
5 a     #A、i、I等皆可，进入编辑模式，将listen_addresses设为'localhost,开发机IP'
6 <Esc>     #返回一般模式
7 4<Enter>     #向下移动4行，定位到#port=5432，同样删除前面的井号
8 :wq     #保存并退出vi

开放端口centos7之后使用firewall：

firewall-cmd  --permanent --zone=public --add-port=5432/tcp

另外还要修改pg_hba.conf文件，允许开发机连接（窃以为这里和postgresql.conf的listen_addresses稍有重复了），这里就不细说了，注意使用md5方式，表示客户端需要使用用户名和密码（加密）连接服务端。重启PostgresQL。

pg_ctl -D /var/lib/pgsql/9.5/data restart

最后修改默认用户postgres的密码。

# sudo -u postgres psql
postgres=# ALTER USER postgres WITH PASSWORD 'postgres';

EF CodeFirst with PostgreSQL（暂缓）

后记：为什么要选择PostgreSQL？关系数据库，博主接触最多的是SQLSERVER和MYSQL，目前基本上已经告别SQLSERVER，你懂的；MYSQL号称最流行，这点毋庸置疑，但如此流行的原因未必是因为最好的，或者最适用的。在MYSQL里面做递归（递归不是SQL标准），基本上多少都是个坑，似乎也不太跟得上时代的脚步，对NoSQL的支持薄弱，如果你说它只要做好关系型数据库的本分，那么某些SQL标准尚不支持，比如LATERAL 。而PostgreSQL号称是全球/宇宙最先进的数据库，虽有夸大其词之嫌，确实功能比较全面，而且开源，开源协议是MIT，比MYSQL的GPL来得更自由。

更多MongoDB相关教程见以下内容：

CentOS 编译安装 MongoDB与mongoDB的php扩展

CentOS 6 使用 yum 安装MongoDB及服务器端配置

Ubuntu 13.04下安装MongoDB2.4.3

MongoDB入门必读(概念与实战并重)

Ubunu 14.04下MongoDB的安装指南

《MongoDB 权威指南》(MongoDB: The Definitive Guide)英文文字版[PDF]

Nagios监控MongoDB分片集群服务实战

基于CentOS 6.5操作系统搭建MongoDB服务

MongoDB 的详细介绍：请点这里
MongoDB 的下载地址：请点这里

本文永久更新链接地址： 

相关内容

    暂无相关文章