PostgreSQL全文检索简介

PostgreSQL自带有一个简易的全文检索引擎，可以实现小规模数据量的全文检索功能。本文我们将引导介绍一下这个功能，对于小数据量的搜索这个功能是足够使用的，而无需搭建额外的ES等重量级的全文检索服务器。

专注于为中小企业提供网站建设、成都网站建设服务,电脑端+手机端+微信端的三站合一,更高效的管理,为中小企业陆良免费做网站提供优质的服务。我们立足成都，凝聚了一批互联网行业人才，有力地推动了上1000家企业的稳健成长，帮助中小企业通过网站建设实现规模扩充和转变。

PG的全文检索操作符是 @@ ，当一个 tsvector (文档)和 tsquery (条件)匹配时返回 true ，并且前后顺序无影响:

和普通的SQL查询一样，只要在 WHERE 条件中使用这个符号就代表使用全文检索条件筛选文档了。如:

@@ 操作符支持隐式转换，对于 text 类型可以无需强类型转换( ::tsvector 或 to_tsvector(config_name, text) )，所以这个操作符实际支持的参数类型是这样的:

tsquery 查询条件并不是简单的正则，而是一组搜索术语，使用并且使用布尔操作符 （AND）、 | （OR）和 ! （NOT）来组合它们，还有短语搜索操作符 - （FOLLOWED BY）。更详细的语法参见 此文档 。

此外，PostgreSQL还提供了两个相对简化的版本 plainto_tsquery 和 phraseto_tsquery 。

plainto_tsquery ( plainto_tsquery([ config regconfig, ] querytext text) returns tsquery )用户将未格式化的 text 经过分词之后，插入 符号转为 tsquery :

phraseto_tsquery ( phraseto_tsquery([ config regconfig, ] querytext text) returns tsquery )行为和 plainto_tsquery 行为类似，但是分词之后不是插入 而是 - (FOLLOWED BY):

使用索引可以加快全文检索的速度。对于全文检索来说，可选的索引类型是 GIN (通用倒排索引)和 GIST (通用搜索树)，官方文档更推荐使用 GIN索引 。创建一个 GIN 索引的范例:

也可以是一个连接列:

还可以单独创建一个 tsvector 列，为这个列创建索引:

除了普通的 ORDER BY 条件之外，PostgreSQL为全文检索提供了两个可选的排序函数 ts_rank([ weights float4[], ] vector tsvector, query tsquery [, normalization integer ]) returns float4 和 ts_rank_cd([ weights float4[], ] vector tsvector, query tsquery [, normalization integer ]) returns float4 ，以便实现基于 权重 的排序。

此外，对于PostgreSQL 9.6以上的版本还可以使用 RUM index 排序。(注意，这个是扩展，默认不包含)。

PostgreSQL默认的分词字典中并不包含中文分词字典，因此我们必须手工引入。目前一个比较好的项目是 zhparser ，同时这个插件也是阿里云的RDS默认包含的。安装和启用没什么好说的。值得一提的是分词配置参数。

在 CREATE EXTENSION 之后，必须配置分词参数才能正确进行分词和查找，否则什么都查不到。官方文档提供的一个配置策略是:

n,v,a,i,e,l 这几个字母分别表示一种token策略，只启用了这几种token mapping，其余则被屏蔽。具体支持的参数和含义可以用 \dFp+ zhparser 显示:

WITH simple 表示词典使用的是内置的simple词典，即仅做小写转换。根据需要可以灵活定义词典和token映射，以实现屏蔽词和同义词归并等功能。

比如我们看下面这个例子:

可以看到 江淮 这个词组在查询的时候被忽略了，我们启用 j (abbreviation,简称)再看看结果:

所以实际使用中要设置合理的token types，过少将导致搜索结果不准确，过多将导致性能下降。此外，还有一些诸如 短词复合: zhparser.multi_short = f 这一类的控制分词结果的选项，根据实际使用酌情开启。

卸载了postgreSQL 9.6，结果再次安装，pgAdmin4中还是存在以前版本的链接，这是什么原因？

这是因为没有卸载干净，卸载之后建议将安装目录删除，将%appdata%中postgresql相关的文件夹删除，将我的文档中的postgresql相关的文件夹删除。

Linux 下常用的(软件包)安装源

我最常用的 Linux 发行版为 CentOS。为了拥有一个“干净”的系统，尽量减少不必要的软件安装，我一般都会采用 minimal 版本安装。但安装完成后并不能满足我的基本需求；对我来说，还需要安装一些常用的（对我来说）软件包。而我需要有很多软件，在CentOS 自带的yum 源中并没有。所以就促使我去寻找其他的可用 yum 软件源。

企业级 Linux 扩展软件包（Extra Packages for Enterprise Linux 即：EPEL）是一个Fedora特殊兴趣小组为企业级 Linux创建、维护和管理的高质量的附加软件包，企业级Linux 包括但不限于 Red Hat Enterprise Linux(RHEL)，CentOS，Scientific Linux（SL）和 Oracle Linux（OL）。

我一般是在 fedora 官网获取 epel-release 软件： 。

我在 CentOS 6 系统下会使用如下指令安装 epel-release

Remi repository 是包含最新版本 PHP 和 MySQL 包的 Linux 源，由 Remi 提供维护。有个这个源之后，使用 YUM 安装或更新 PHP、MySQL、phpMyAdmin 等服务器相关程序的时候就非常方便了。

它依赖 EPEL 软件包。

我一般是在其官网获取 remi-release 软件： Remi's RPM repository

我在 CentOS 6 系统下会使用如下指令安装 remi-relsase:

RepoForge 的前身是 RPMForge 。

RepoForge项目维护Red Hat Enterprise Linux（RHEL），CentOS和Scientific Linux的RPM软件包。它提供了一组与各种RHEL派生版本兼容的存储库。

我一般是在其官网获取 RepoForge （rpmforge-release）软件： RepoForge

我在 CentOS 6 (x86)系统下会使用如下指令安装 rpmforge-release:

以上三个 Linux 软件源已经够我当前使用了，如果以后在Linux使用过程中有用到其他的软件源，我会在此文件下做补充。

很多时候在CentOS系统下默认的Postgresql版本并不能满足需求。如我安装GitLab时就要求Postgresql在9.1以上，但CentOS 系统 6.9 版本仅提供了Postgresql 的 8.4.20 版本，而CentOS 7 默认也无法安装Postgresql 9 及以上版本。

当然你可以使用源码安装，但对于大多数人来说应该会选择一个更简单的方法。那就去给你的系统安装一个postgresql的yum源吧。

网址：

进到首页你应该可以看到以下内容：

选一个Postgresql的发行版本点进去，会看到如下内容：

对应于不同的发行版本，针对不同的操作系统有着不同的yum源。你可以根据你的操作系统进行选择。

比如我选择了 Postgresql 9.6 版本，而我的操作系统是 CentOS 6.9 ，x86 架构。我只需要执行以下命令：

然后在 /etc/yum.repos.d 目录下会多出一个 文件 pgdg-96-centos.repo 。

至此，你就可以使用命令 yum install postgresql96 来安装postgresql 9.6 版本最新的发行版了软件了。

在CentOS6系统中，默认能安装的MySQL最高版本为5.5.x；而到CentOS7中由于Oracle收购MySQL，因担心版权问题，CentOS7放弃了MySQL而采用了Mariadb，最高支持版本为5.5.x。对于用习惯了MySQL的，并且想使用高版本MySQL的我来说，系统默认支持已经满足不了需求。

可以从 这里 选择一个你所需要的yum源进行安装。

然后：

然后你就可以执行以下命令安装 MySQL 5.7了

在MongoDB的官网上可以找到MongoDB的yum安装源配置方式。

MongoDB 官网YUM源配置方式

然后在打开的页面中会有如下信息：

你需要根据自己的需求调整版本号，然后创建一个/etc/yum.repos.d/mongodb-org-y.x.repo文件，把上面的配置写进去。

完成以上步骤后，就可以使用以下命令安装MongoDB了。

如何在w7-64上安装postgresql-9.6.2-1-windows-x64

PostgreSQL安装：

一、windows下安装过程

安装介质：postgresql-9.6.2-1-windows-x64(166M)，安装过程非常简单，过程如下：

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里配置了数据库的几个环境变量，内容如下：

如何在Debian 8/7上安装PostgreSQL 9.6

执行以下命令安装PostgreSQL

$ apt-get install postgresql

安装好之后，PostgreSQL会创建一个名为postgres的系统用户，以及一个同样名字的数据库管理员角色，以及一个同样名字的数据库。

Debian下配置PostgreSQL

通过cat /etc/passwd列出系统中所有的用户，可以找到刚刚PostgreSQL为我们创建的postgres用户：

$ cat /etc/passwd

...

postgres:x:122:129:PostgreSQL administrator,,,:/var/lib/postgresql:/bin/bash

使用su命令切换到postgres用户：

$ su postgres

使用passwd命令修改当前系统用户密码（可选）：

$ passwd

输入新的 UNIX 密码：

重新输入新的 UNIX 密码：

passwd：已成功更新密码

使用createdb命令创建新的数据库：

$ createdb mydb

使用psql命令进入PostgreSQL命令行。psql后面可以接数据库名称，表示进入特定的数据库。如果不提供数据库名称，则默认进入postgres数据库。

$ psql

psql (9.1.24)

输入 "help" 来获取帮助信息

PostgreSQL 高级SQL(五) 内建窗口函数

前面俩个章节我们介绍了窗口函数、滑动窗口函数的概念，接下来我们介绍一下PG支持的原生通用窗口函数，总共11个(9.6版本, 中国社区官网文档地址 ）

1、row_number 函数

row_number函数可以给每隔数据行返回一个虚拟的自增ID,也就是相当于给行分配一个编号，这些编号不会出现重复，即使over()里面没有按照字段排序字段也能正常工作，

2、rank函数

rank的官方解释是：带间隙的当前行排名； 与该行的第一个同等行的row_number相同

从上面的的结果我们可以看出 rank函数和row_number一样可以将行编号，但是号码可能重复，比如我们按照年份排序，年份相同的话rank值相同，2017年的数据rank直接跳到了7，这就相当于上学的时候考试，用rank计算排名的话，如果同年级出现三个并列的第一名的话，那么计算的结果将是三个人的rank值都是第一，但是实际上的第二高的分数的同学会被rank排名为第四名，如果我们想第二高的分数的排名为2,我们可以使用dense_rank函数；

3、dense_rank函数

从上面的结果我们可以看出dense_rank函数会把编号弄得更加紧密，中间不会出现像rank那样的断层编码。

4、percent_rank函数

官方文档解释： 当前行的相对排名=(rank- 1) / (总行数 - 1) ,

    排名和rank值成正相关 ，rank值相同的行号 percent_rank获取的结果也一样，返回的 结果是个小数范围在[0,1]之间 ，可以等于0或者1

5、cume_dist函数

官方文档解释： 当前行的相对排名=(rank- 1) / (总行数 - 1) ，

    排名和rank值成正相关 ，rank值相同的行号 percent_rank获取的结果也一样，返回的 结果是个小数范围在[0,1]之间 ，可以等于0或者1

6、ntile函数

官方文档解释：从1到参数值的整数范围，尽可能等分分区，

ntile(num_buckets)，num_buckets的值表示将结果集分成num_buckets组，有限填满前面的组，最后一组可能出现个数不足(非等分)情况，实际上就是把每隔行分个组号。

7、lag函数

官方文档解释：lag( value  anyelement [,  offset  integer [,  default  anyelement ]])，返回 value ， 它在分区内当前行的之前 offset 个位置的行上计算；如果没有这样的行，返回 default 替代。 (作为 value 必须是相同类型)。  offset 和 default 都是根据当前行计算的结果。如果忽略它们，则 offset 默认是1， default 默认是空值

    官方文档的解释很晦涩难懂，我们直接使用用例执行一下看一下数据分布就好了

从上图可以知道当前行的lag值是当前行的前offset行的值，没有的话就返回default,default不想存在的话就返回null,从数据姐过再去看官方文档的解释的话可能清晰很多，lag函数可以在结果集的行内移动，经常使用到的场景是计算今年和全年的年产量的差值，

8、lead函数

官方文档解释：lead( value  anyelement [,  offset  integer [,  default  anyelement ]]) 返回 value ，它在分区内当前行的之后 offset 个位置的行上计算；如果没有这样的行，返回 default 替代。(作为 value 必须是相同类型)。 offset 和 default 都是根据当前行计算的结果。如果忽略它们，则 offset 默认是1， default 默认是空值

其实lead函数和lag函数的作用是相同的，如果lead的offset参数值为-N,那么lag的offset的参数为N的话计算结果是相同的，lag(gdp,-1)是lead(gdp,1)的替代

9、first_value、last_value、nth_value函数较为简单不做介绍

至此我们讲完了几乎所有的窗口函数，希望这五篇关于PostgreSQL的文章能对大家在平时的开发中有所帮助