PostgreSQL学习笔记（一）

前言

最近在各大招聘要求或者技术博客中，总有看到Postgres/PG/PostgreSQL这些词，一眼下去完全不知道是什么来的，冲浪一下发现这也是一个类似MySQL的开源数据库，同时近年来在国外还不断在变得流行起来，通过下图的排名可以看到PostgreSQL已经连续几个季度在受欢迎程度第4名了。

那么下面就去了解一下这是啥东东O(∩_∩)O

PS：Postgres（历史叫法）、PostgreSQL（官方全称）、PG（缩写）指的都是同一个东西

在去了解之前我有如下疑问

1. PostgreSQL是什么？
2. PostgreSQL和MySQL有什么区别？
3. 为什么感觉PostgreSQL听得比较少？是最近才开发出来的吗？
4. 如何使用PostgreSQL？

PostgreSQL是什么？

from GPT

PostgreSQL是一个高度先进的开源关系数据库管理系统（RDBMS）。它的发展始于1986年，最初作为加州大学伯克利分校计算机系的一个项目，后来发展成为一个全面支持SQL语言的数据库系统，并且随着时间的推移，它已经发展成为一个功能丰富、性能卓越、支持高度可扩展性的数据库解决方案。PostgreSQL广泛用于从小型应用到大型互联网应用以及数据仓库等多种场合。

核心特性

PostgreSQL的核心特性包括但不限于以下几点：

1. 复杂的查询能力：PostgreSQL支持复杂的查询（如子查询、连接等），提供了强大的数据检索能力。
2. 数据完整性：支持主键、外键、唯一约束和检查约束等多种数据完整性约束，确保数据的准确性和一致性。
3. 事务和并发控制：支持ACID（原子性、一致性、隔离性、持久性）事务模型。通过MVCC（多版本并发控制）机制，优化并发性能，减少锁的争用。
4. 高度可扩展：可以通过增加更多的硬件资源来扩展系统的性能和容量，同时支持分区表、复制等功能，提高数据处理能力和高可用性。
5. 支持多种编程语言：提供了丰富的API，支持C/C++、Java、Python、Perl、Ruby等多种编程语言的接口。
6. 高级索引：支持B树、GiST（通用搜索树）、GIN（通用倒排索引）等多种索引类型，以及全文搜索等高级功能。
7. 扩展性：提供了存储过程、自定义函数、自定义数据类型等机制，允许用户根据需要扩展数据库功能。
8. 安全性：支持SSL加密连接、角色基础的权限管理等多种安全机制，保证数据的安全性。

应用场景

PostgreSQL因其高度的灵活性和功能丰富性，被广泛应用于多种场景，如：

• Web应用：作为后端数据库存储Web应用的数据。
• GIS系统：通过PostGIS扩展，PostgreSQL成为存储和查询地理空间数据的理想选择。
• 数据仓库：因其优秀的并发控制和查询优化能力，适合用作数据仓库，处理大量数据的存储和分析。
• 金融系统：其事务处理的可靠性和数据完整性特性，使其成为金融系统中存储关键数据的可靠选择。

社区和发展

PostgreSQL拥有一个活跃的开源社区，不断有来自世界各地的开发者为其贡献代码，推出新的功能。社区提供的丰富文档、教程和支持是PostgreSQL得以持续发展和改进的重要保障。此外，也有众多公司和组织提供专业的PostgreSQL商业支持和咨询服务，以满足企业级应用的需求。

总之，PostgreSQL是一个功能强大、高度可定制和扩展的数据库系统，适合用于各种规模和类型的项目。

总结一下就是PostgreSQL是一个开源、对象关系数据库管理系统（ORDBMS）、支持多种复杂查询、事务完整性、多版本并发控制、强大扩展性、多种数据类型。

PostgreSQL和MySQL的区别

可以看到PostgreSQL相对于MySQL提供了更多的特性和功能，同时也支持多种数据类型，例如：几何、数组、JSONB、XML等。在索引上也有GIN索引、B-树索引、哈希索引、GiST索引、Bloom过滤器索引等。

为什么感觉PostgreSQL听得比较少？

通过一波网络冲浪，总结下来主要是两个原因，学习成本和使用成本比较大

1. 对于国内大部分公司或者开发者来说，项目中都使用不到PostgreSQL的高级特性，例如JSONB、数组、几何等数据类型，常用的int、varchar、datetime已经足够使用。
2. PostgreSQL强大的索引类型，例如倒排索引、全文索引、空间索引，在各种后台系统、CRUD项目中也用不着，只对几个关键列建个B树索引就够用了。就算需要使用到全文搜索、时序之类的，也更倾向于使用专门领域的组件，例如ElasticSearch、Prometheus等。
3. PostgreSQL对高并发支持良好，但使用的是多进程模式，这就导致进程创建会比较慢、同时占用内存会比较多。
4. 以前项目都是大部分都是OLMP一把嗦，即Apache加MySQL加PHP，同时MySQL又开箱即用，对服务器要求不高，导致大部分开发者接触的第一个数据库就是MySQL，那就会不断影响传递，”祖传代码”就出来了，毕竟能跑就行。

安装

PostgreSQL支持多种系统，例如Linux、MacOS、WIndows都是支持的，下面是MacOS的一种安装方式。

1
2
3
4
5
6
7
8
9

// 使用brew安装
brew install postgresql

// 启动postgreSQL并设置为自启动
brew services start postgresql

// 查看安装是否成功和版本号
postgres --version
// postgres (PostgreSQL) 14.11 (Homebrew)

基础操作

由于PostgreSQL和MySQL都是遵循SQL标准的关系型数据库管理系统（RDBMS），所以他们的大致语法操作都差不多，下面就简单试一试。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48

// 创建数据库
createdb mydb

// 进入数据库
psql mydb

// 查看版本号
select version();
                                                            version
-------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 14.11 (Homebrew) on x86_64-apple-darwin22.6.0, compiled by Apple clang version 15.0.0 (clang-1500.1.0.2.5), 64-bit
(1 row)

// 查看集群内所有数据库
\\l 或者 select datname from pg_database;

// 创建表
create table users(
	id int UNIQUE, -- 用户id，唯一约束
	username varchar(128) NOT NULL, -- 用户名，非空约束
	create_time date, -- 创建时间
	score real DEFAULT 100 -- 分数，默认值为100
);

// 删除表
drop table users

// 修改表、例如新增列、删除列、新增约束、删除约束
ALTER TABLE XXX

// 插入数据，需要注意非简单数字常量，需要使用单引号进行包裹
insert into users values(1,'tom','2024-04-24',99.5);

// 查询数据
select * from users;

// todo where条件查询、联表查询、limit、order by

// 更新数据
update users set username  = 'TOM', score = 100 where id = 1;

// 删除数据
delete from users where id = 1;

// 高级特性-创建视图
create view myview as select id,username from users;

// todo 外键-事务、窗口函数 后面再看看 ⭐

常见数据类型

数字类型

字符类型

时间类型

布尔类型

参考资料

• PostgreSQL 9.5.3 中文手册
• Postgres 在中国还能起来么？ - V2EX
• postgresql很强大，为何在中国，mysql成为主流？ - Ricardo_ML - 博客园 (cnblogs.com)
• PostgreSQL 与 MySQL — 关系数据库管理系统（RDBMS）之间的区别 — AWS (amazon.com)