PostgreSQL 初探

Stella981
• 阅读 639

PostgreSQL 是我们经常选择的数据库之一。它不仅仅是关系型数据库,同时也添加了对JSON数据的支持全文检索功能,以及其他扩展。

2020 年 2月的总排名为第四名,关系型数据库中排名第四名。排名信息参考:DB-Engines

PostgreSQL 初探

PostgreSQL 初探

为了更好地应用的工作中,针对 PostgreSQL 给我造成的疑惑整理了如下内容:

(1) PostgreSQL 版本之间的主要区别

(2) PostgreSQL 支持的数据类型

(3) PostgreSQL 存储引擎Heap介绍

(4) 围绕 JSON/JSONB 的全文检索功能以及 12.x 中的 JSON/JSONB Path

1, PostgreSQL 版本之间的主要区别

2020 年 2 月当我们访问 PostgreSQL 官网的时候,通过看到多个 Release 版本,应该怎么选择呢?

PostgreSQL 初探

PostgreSQL 9.x 版本在 2020 年 2 月的版本是计划中的左右一个 release 版本,所以翻阅了 PostgreSQL 的相关文档后,整理了从 10.X,11.X,12.X 这三个版本的信息。如下图:

PostgreSQL 初探

通过上面的版本我们发现,给开发人员带来的变化不大,这里列出了 4 项,开发人员经常关注的技术点:

(1)当前支持的数据类型

(2)存储引擎 Heap 具体的特性

(3)围绕 JSON/JSONB 的全文检索功能以及 12.x 中的 JSON/JSONB Path

所以下文将主要研究上面是 4 个技术点。

这里使用了一个 12.2 版本的 PostgreSQL 的 Docker 镜像:

docker pull postgre:12.2

如果对其他版本感兴趣可以访问Docker hub

启动 Postgre

docker run -e POSTGRES_PASSWORD=123456 -p 5432:5432 --name potgre12 postgres:12.2

其中的 -e POSTGRES_PASSWORD=123456 是设置 superuser 用户的密码,superuser 的默认 username 是 postgres.

PS:如果不想通过密码访问也可以通过 POSTGRES_HOST_AUTH_METHOD=trust 来允许不需要密码访问。

安装 psql 客户端(如果你还是用其他客户端可以跳过该步骤)

brew install libpq

连接 postgreSQL

psql -h 127.0.0.1 -p 5432 -U postgres --passsword

2,PostgreSQL 支持数据类型

PostgreSQL 中支持多种数据类型。如下图

PostgreSQL 初探

3,PostgreSQL 存储引擎 Heap 具体特性

PostgreSQL 的存储引擎 Heap 和 MySQL 的 InnoDB 都完全遵循ACID,使用的是行级锁。

4,围绕 JSON/JSONB 的全文检索功能以及 12.x 中的 JSON/JSONB Path

JSON 和 JSONB 的区别在于:

(1) JSON 类型以 JSON 格式存储文本数据,在查询的时候需要先做内容解析,然后进行查询。所以写入快、查询慢。

(2)JSONB 类型以 JSON 格式的二进制形式存储数据,在插入的时候需要做文本解析,查询的时候并不需要做解析,因此写入慢,查询快。

而对 JSON/JSONB 涉及到内容包括,下面的内容对对一条数据的一个简单例子:建表、新建数据、查询、模糊查询、数据更新。

(1)建表

create table boy (
    id int primary key not null, 
    name char(30) not null, 
    information jsonb
);

(2)新建数据

insert into boy (id, name, information) values (1,'Page', '{"age":18}');
insert into boy (id, name, information) values (2,'Aaron', '{"age":18}');
insert into boy (id, name, information) values (3,'Join', '{"age":18, "address":"beijing dongchengqu"}');

(3)查询

# 从 9.4 版本只是的通过 操作符 来查询 age 等于 18 的 student

select * from boy where (information->>'age')::int=18;


# 从 9.4 版本只是的通过 操作符 来查询 address 中以 beijing 开头的 students

select * from boy where (information->>'address')::text like 'beijing%';


# 12.x版本支持的 JSON/JSONB Path 方式查询 age 等于 18 的 student

select jsonb_path_query(information, '$.age ? (@==18)') from boy;


# 12.x版本支持的 JSON/JSONB Path 方式查询 address 中以 beijing 开头的 students

select jsonb_path_query(information, '$.address ? (@ starts with "beijing")') from boy;

通过上面的代码不难发现,PostgreSQL 12 中提供的 JSON function 非常好用。更多查询语句可以参考: 官网 json functions

(4)模糊查询(全文检索)

经过尝试默认条件下 PostgreSQL 提供的分词器对中文分词并不好。所以下面将演示英文的全文检索

新建表

create table technical (
   id int primary key not null,
   name tsvector
);

其中 tsvector 的数据类型是是分词器类型。可以执行 select 'hello page'::tsvector;来查看分词结果。

插入数据

insert into technical (id, name) values (1, 'Test Driven Development'), (2, 'Test Driven Design'), (3, 'Behave Driven Development');

当搜索关键词 Test和Driven 时应该是显示 2 条结果;

select * from technical where name@@'Test & Driven';

总结:

通过上面的几个特性的分析,不难发现 PostgreSQL 提供了强大的功能和函数,上手之前还是需要对其中的函数有个大致的了解。

参考

(1) PostgreSQL 中文网

点赞
收藏
评论区
推荐文章
blmius blmius
3年前
MySQL:[Err] 1292 - Incorrect datetime value: ‘0000-00-00 00:00:00‘ for column ‘CREATE_TIME‘ at row 1
文章目录问题用navicat导入数据时,报错:原因这是因为当前的MySQL不支持datetime为0的情况。解决修改sql\mode:sql\mode:SQLMode定义了MySQL应支持的SQL语法、数据校验等,这样可以更容易地在不同的环境中使用MySQL。全局s
Wesley13 Wesley13
3年前
java将前端的json数组字符串转换为列表
记录下在前端通过ajax提交了一个json数组的字符串,在后端如何转换为列表。前端数据转化与请求varcontracts{id:'1',name:'yanggb合同1'},{id:'2',name:'yanggb合同2'},{id:'3',name:'yang
皕杰报表之UUID
​在我们用皕杰报表工具设计填报报表时,如何在新增行里自动增加id呢?能新增整数排序id吗?目前可以在新增行里自动增加id,但只能用uuid函数增加UUID编码,不能新增整数排序id。uuid函数说明:获取一个UUID,可以在填报表中用来创建数据ID语法:uuid()或uuid(sep)参数说明:sep布尔值,生成的uuid中是否包含分隔符'',缺省为
待兔 待兔
3个月前
手写Java HashMap源码
HashMap的使用教程HashMap的使用教程HashMap的使用教程HashMap的使用教程HashMap的使用教程22
Jacquelyn38 Jacquelyn38
3年前
2020年前端实用代码段,为你的工作保驾护航
有空的时候,自己总结了几个代码段,在开发中也经常使用,谢谢。1、使用解构获取json数据let jsonData  id: 1,status: "OK",data: 'a', 'b';let  id, status, data: number   jsonData;console.log(id, status, number )
Easter79 Easter79
3年前
sql注入
反引号是个比较特别的字符,下面记录下怎么利用0x00SQL注入反引号可利用在分隔符及注释作用,不过使用范围只于表名、数据库名、字段名、起别名这些场景,下面具体说下1)表名payload:select\from\users\whereuser\_id1limit0,1;!(https://o
Java修道之路,问鼎巅峰,我辈代码修仙法力齐天
<center<fontcolor00FF7Fsize5face"黑体"代码尽头谁为峰,一见秃头道成空。</font<center<fontcolor00FF00size5face"黑体"编程修真路破折,一步一劫渡飞升。</font众所周知,编程修真有八大境界:1.Javase练气筑基2.数据库结丹3.web前端元婴4.Jav
Stella981 Stella981
3年前
Python3:sqlalchemy对mysql数据库操作,非sql语句
Python3:sqlalchemy对mysql数据库操作,非sql语句python3authorlizmdatetime2018020110:00:00coding:utf8'''
Wesley13 Wesley13
3年前
MySQL部分从库上面因为大量的临时表tmp_table造成慢查询
背景描述Time:20190124T00:08:14.70572408:00User@Host:@Id:Schema:sentrymetaLast_errno:0Killed:0Query_time:0.315758Lock_
Python进阶者 Python进阶者
9个月前
Excel中这日期老是出来00:00:00,怎么用Pandas把这个去除
大家好,我是皮皮。一、前言前几天在Python白银交流群【上海新年人】问了一个Pandas数据筛选的问题。问题如下:这日期老是出来00:00:00,怎么把这个去除。二、实现过程后来【论草莓如何成为冻干莓】给了一个思路和代码如下:pd.toexcel之前把这