postgresql属性的简单介绍

postgresql字段值唯一约束

postgresql 允许设置多个字段为值唯一的约束。

10年积累的成都网站设计、做网站经验，可以快速应对客户对网站的新想法和需求。提供各种问题对应的解决方案。让选择我们的客户得到更好、更有力的网络服务。我虽然不认识你，你也不认识我。但先网站设计后付款的网站建设流程，更有石城免费网站建设让你可以放心的选择与我们合作。

使用 pgAdmin 4 设置起来很方便。而且在 postgresql 6.5.1 是默认安装就具备的工具。

首先找到你要设置约束的表

鼠标右键选择属性

选择强制约束

点击 + 号 新增一个约束

输入内容，选择要设置的字段

最后点击 保存按钮

字段值唯一约束就建立成功了。

之后你就会发现，除非是空值，否则你设置的字段内是不会有重复值的。

postgresqL 的Btree 与gin索引

一．gin索引需要安装第三方插件

yum install postgresql96-contrib -- 安装插件

find / -name extension --可以看到btree_gin.control存在

create extension btree_gin; -- 添加索引

二.测试数据基本属性介绍

总共使用3个表，表结构和数据量完全一致。

表数据量：10522369

表字段：id ,basic_acc_no,id_card,name,sex,telephone,json_t

1）索引的配置情况：

basic_account_info_al -- btree

basic_account_info_al2 --gin

basic_account_info_al3 -- btree multi

basic_account_info_al 单列索引 id，basic_acc_no，name,json_t

basic_account_info_al2 gin索引 (id,basic_acc_no,id_card,name),(json_t)

basic_account_info_al3 复合索引 (id,basic_acc_no),(name,id)(json_t,id)

basic_account_info_al 表达式索引 (json_t-id)

basic_account_info_al2表达式索引 ((json_t-'id'))

三．测试结果

1.唯一值属性：索引字段都是唯一 id，basic_acc_no

查询语句

explain analyse select * from basic_account_info_al2 where id = 29699221 ;

explain analyse select * from basic_account_info_al where id = 29699221 ;

explain analyse select * from basic_account_info_al3 where id = 29699221 ;

explain analyse select * from basic_account_info_al2 where basic_acc_no = 'XFK2990134' ;

explain analyse select * from basic_account_info_al where basic_acc_no = 'XFK2990134' ;

explain analyse select * from basic_account_info_al3 where basic_acc_no = 'XFK2990134' ;

explain analyse select * from basic_account_info_al2 where basic_acc_no = 'XFK9780134' and id = 29699221;

explain analyse select * from basic_account_info_al where basic_acc_no = 'XFK9780134' and id = 29699221;

explain analyse select * from basic_account_info_al3 where basic_acc_no = 'XFK9780134' and id = 29699221;

explain analyse select * from basic_account_info_al2 where id = 29699221 and basic_acc_no = 'XFK9780134' ;

explain analyse select * from basic_account_info_al where id = 29699221 and basic_acc_no = 'XFK9780134' ;

explain analyse select * from basic_account_info_al3 where id = 29699221 and basic_acc_no = 'XFK9780134' ;

2.重复值属性： name是有重复值的。

explain analyse select * from basic_account_info_al where name ='张燕洪';

explain analyse select * from basic_account_info_al3 where name ='张燕洪';

explain analyse select *from basic_account_info_al2 where name ='张燕洪';

explain analyse select * from basic_account_info_al2 where id = 24426014 and name = '周杨' ;

explain analyse select * from basic_account_info_al where id = 24426014 and name = '周杨' ;

explain analyse select * from basic_account_info_al3 where id = 24426014 and name = '周杨' ;

explain analyse select * from basic_account_info_al2 where name = '周杨' and id = 24426014 ;

explain analyse select * from basic_account_info_al where name = '周杨' and id = 24426014 ;

explain analyse select * from basic_account_info_al3 where name = '周杨' and id = 24426014 ;

3.jsonb属性

create index inx_gin_json on basic_account_info_al2 using gin (json_t);

create index inx_btree_json on basic_account_info_al (json_t);

create index inx_btree_2_js on basic_account_info_al3 (json_t,id );

explain analyse select * from basic_account_info_al where json_t ='{"id": 21782879, "sex": 0, "name": "刘乐典"}';

explain analyse select * from basic_account_info_al2 where json_t ='{"id": 21782879, "sex": 0, "name": "刘乐典"}';

explain analyse select * from basic_account_info_al3 where json_t ='{"id": 21782879, "sex": 0, "name": "刘乐典"}';

explain analyse select * from basic_account_info_al WHERE json_t @ '{"id": 21782879}';

explain analyse select * from basic_account_info_al2 WHERE json_t @ '{"id": 21782879}';

explain analyse select * from basic_account_info_al3 WHERE json_t @ '{"id": 21782879}';

explain analyse select * from basic_account_info_al where (json_t-id)= '24426014' ;

explain analyse select * from basic_account_info_al2 where (json_t-id)= '24426014' ;

explain analyse select * from basic_account_info_al3 where (json_t-id)='24426014' ;

4.jsonb表达式索引

查询条件 表名 查询时使用的索引名称 查询时间（5次平均）/ms

(json_t-id)= '24426014' basic_account_info_al inx_json_id 0.040

basic_account_info_al3 inx_json_id_2 0.039

explain analyse select * from basic_account_info_al where (json_t-id)= '24426014' ;

explain analyse select * from basic_account_info_al2 where (json_t-id)= '24426014' ;

四．获相同的结果使用Jsonb与btree对比

jsonb支持两种特有的GIN索引jsonb_ops和jsonb_path_ops。 jsonb_ops调用gin_extract_jsonb函数生成key，每个键和值都作为一个单独的索引项。而jsonb_path_ops使用函数gin_extract_jsonb_path抽取：只为每个值创建一个索引项。{“foo”:{“bar”,”baz”}}, jsonb_ops生成3个索引项，jsonb_path_ops由foo,bar,baz组合一个hash值作为一个索引项。jsonb_path_ops索引要比jsonb_ops的小很多，性能上也会有所提升。

create index inx_gin_patn_json ON public.basic_account_info_al4 USING gin (json_t jsonb_path_ops); -- jsonb_path_ops

create index inx_gin_json on basic_account_info_al2 using gin (json_t); --jsonb_ops

1.精确查询

2.范围查询

下表显示了gin索引对于jsonb数据类型可使用的操作符。

名称 索引数据类型 可索引操作符

jsonb_ops jsonb ? ? ?| @

json_path_ops jsonb @

注：? ? ?| 索引key是否包含在jsonb中

对于范围(json_t-'id') 20000079，这样的条件 gin索引不起作用， 这里采用表达式索引方式，查询条件的两边数据类型相同才可以做索引查询，否则全表扫描。

CREATE INDEX inx_json_id_2 ON public.basic_account_info_al2 USING btree (((json_t-'id')::int));

总结： 当仅有一个条件查询时，gin索引与btree索引的性能差异不大，但有多个条件查询时，gin,btree单

列索引没有btree复合索引的性能高。jsonb是以二进制格式存储且不保证键的顺序。可以使用表达式索引指定到jsonb的具体键值，但是如果不能提前知道查询数据中的哪个键，确定定义GIN索引和使用@（或者其他有利于索引的操作符）查询。

五．jsonb添加数据属性

例如：

{"id":20000241,"name":"陈敏","sex":1} - {"age":"18","id":20000241,"name":"陈敏","sex":1}

一旦创建了索引，就不需要进一步的干预：当表被修改时，系统将更新索引，当执行计划认为使用索引比顺序的表扫描更有效的时候，它会使用索引。

UPDATE basic_account_info_al4 SET json_t = json_t || '{"age":"18"}'::jsonb; -- 更新语句

gin索引名称 索引方式 修改前大小 修改后大小 带索引更新时间

inx_gin_patn_json jsonb_path_ops 574M 615M 643561.004 ms

inx_gin_json jsonb_ops 665M 695M 时间过长超过1h

jsonb_ops方式建立的索引大量更新时，执行时间太长。当插入更新时gin索引比较慢，如果要向一张大表中插入大量数据时，最好先把gin索引删除，插入数据后再重建索引。

当json_t为{"id":20000241,"name":"陈敏","sex":1} 数据量为10522369 创建gin索引时间

130372.955 ms

当json_t为{"age":"18","id":20000241,"name":"陈敏","sex":1} 数据量为10522369 创建gin索引时间

148971.011 ms

如何安装PostgreSQL

PostgreSQL安装：

一、windows下安装过程

安装介质：postgresql-9.1.3-1-windows.exe（46M），安装过程非常简单，过程如下：

1、开始安装：

2、选择程序安装目录：

注：安装 PostgreSQL 的分区最好是 NTFS 格式的。PostgreSQL 首要任务是要保证数据的完整性，而 FAT 和 FAT32 文件系统不能提供这样的可靠性保障，而且 FAT 文件系统缺乏安全性保障，无法保证原始数据在未经授权的情况下被更改。此外，PostgreSQL 所使用的"多分点"功能完成表空间的这一特征在FAT文件系统下无法实现。

然而，在某些系统中，只有一种 FAT 分区，这种情况下，可以正常安装 PostgreSQL，但不要进行数据库的初始化工作。安装完成后，在 FAT 分区上手动执行 initdb.exe 程序即可，但不能保证其安全性和可靠性，并且建立表空间也会失败。

3、选择数据存放目录：

4、输入数据库超级用户和创建的OS用户的密码

注：数据库超级用户是一个非管理员账户，这是为了减少黑客利用在 PostgreSQL 发现的缺陷对系统造成损害，因此需要对数据库超级用户设置密码，如下图所示，安装程序自动建立的服务用户的用户名默认为 postgres。

5、设置服务监听端口，默认为5432

6、选择运行时语言环境

注：选择数据库存储区域的运行时语言环境（字符编码格式）。

在选择语言环境时，若选择"default locale"会导致安装不正确；同时，PostgreSQL 不支持 GBK 和 GB18030 作为字符集，如果选择其它四个中文字符集：中文繁体 香港（Chinese[Traditional], Hong Kong S.A.R.）、中文简体 新加坡（Chinese[Simplified], Singapore）、中文繁体 台湾（Chinese[Traditional], Taiwan）和中文繁体 澳门（Chinese[Traditional], Marco S.A.R.），会导致查询结果和排序效果不正确。建议选择"C"，即不使用区域。

----我选择了default localt，安装正确；建议选择default localt。

7、安装过程（2分钟）

8、安装完成

安装完成后，从开始文件夹可以看到：

在安装目录可以看到：

其中：data存放数据文件、日志文件、控制文件、配置文件等。

uninstall-postgresql.exe用于卸载已安装的数据库管理系统。

pg_env.bat里配置了数据库的几个环境变量，内容如下：

二、pgAdmin（大象）

对于每种数据库管理系统，都有相当多的设计与管理工具（可视化界面管理工具），有的是数据库厂商自己提供的（一般都至少有一个），有的是第三方公司开发的，你甚至可以自己写一个简单易用的管理工具。例如Oracle的Oracle SQL Developer（自己开发的）、PLSQL Developer（第三方公司开发的）、SQL Server Management Studio（自己开发的）、（开源中国）网站上提供的个人或组织开发的简易小巧的管理工具。

PostgreSQL就有好几款流行的管理工具，例如：pgAdmin、navicat_pgsql、phppgsql等。

pgAdmin是一个针对PostgreSQL数据库的设计和管理接口，可以在大多数操作系统上运行。软件用C++编写，具有很优秀的性能。

pgadmin 是与 Postgres 分开发布的，可以从下载。目前装个全功能的PostgreSQL数据库，自带该管理工具。

打开pgAdmin，可以看到在第一部分安装的本地数据库的属性，如下图所示：

图中可以看出，新安装的PostgreSQL数据库管理系统带有一个数据库postgres；已建好两个表空间：pg_default、pg_global。

initdb.exe初始化的两个默认表空间pg_global、pg_default。数据库默认的表空间pg_default 是用来存储系统目录对象、用户表、用户表index、和临时表、临时表index、内部临时表的默认空间，他是模板数据库template0和template1的默认表空间。initdb.exe初始化的两个默认表空间pg_global、pg_default。数据库默认的表空间pg_global是用来存储共享系统目录的默认空间。

pg_default 为 PostgreSQL也可以理解成系统表空间，它对应的物理位置为 $PGDATA/base目录。

在PostgreSQL（pg_catalog）下可以看到postgers数据库的一些数据字典和数据字典视图。

新建一个服务器连接，连接远程linux服务器上的PostgreSQL数据库（假设已有远程linux上已安装好PostgreSQL数据库管理系统）：

得到数据库属性如下图所示：

图中可以看出，该远程数据库管理系统上建有两个数据库：postgres、cpost；四个表空间：pg_default、pg_global、pis_data、pis_index。

三、pgsql

对于每种数据库管理系统，都会提供一个命令行管理接口，例如Oracle的sqlplus，SQL Server的isql和osql等。

凡是用图形管理界面可以实现的功能原则上都可以通过命令行界面命令实现。两者各有优缺点，使用场合不同。在windows下当然常用图形管理界面，因为在图像管理界面中往往都嵌有命令行工具，而在unix和linux下，当然就常用命令行工具了，除了我们在类unix下主要使用字符界面的原因外，还因为大部分情况下我们只能通过telnet或ssh工具远程连接服务器进行操作，此时也只能使用命令行了。

从开始目录打开SQL shell（pgsql），该可执行程序为E:\PostgreSQL\bin\psql.exe。输入密码得到如下图界面：

也可以在修改了系统环境变量Path之后（增加PostgreSQL\bin目录），从命令行直接启动pgsql。

至此，PostgreSQL在Windows下安装完毕。