PostgreSQL 入门到入土知识分享一

铁头宏

1.业务背景

最近开发大哥在一个项目开发中一反常态地选择了pgsql，当我满脸疑惑地去询问大哥为什么要选择这个数据库时，只听大哥两句话震惊到我：“pgsql是最先进的数据库”，“它可以按照你的意念执行sql”,,,


本着活到老学到老的原则，笔者去了解了pgsql的诸多特性，有了此篇技术分享
参考文献：
https://www.zhihu.com/column/c_1452567507496689664
https://blog.csdn.net/y666666y/article/details/117460955
https://www.cnblogs.com/liqiu/p/4283560.html
https://www.learnfk.com/postgresql/postgresql-hstore.html
https://www.runoob.com/postgresql/postgresql-constraints.html
中文手册：http://shouce.jb51.net/postgresql/

辅助工具：Postgres 15
2.PostgreSQL诸多特性

2.1定义

PostgreSQL最早是以加州大学伯克利分校计算 机系开发的 POSTGRES, Version 4.2 为基础的对象关系型数据库管理系统(ORDBMS)，完全由社区驱动的开源项目，由全世界超过1000名贡献者所维护。
2.2 开源性

PostgreSQL 是一个免费并且开源的软件。它提供了单个完整功能的版本，不像MySQL那样提供了 多个不同的社区版、商业版与企业版。它基于自由的BSD/MIT许可，组织可以使用、复制、修改和重新分发代码，甚至包装一下拿出去卖钱，只需要提供一个版权声明即可。这一点相对于 MySQL 社区版的 GPL 协议友好许多。
2.3先进性

• PostgreSQL 使用 C 语言进行开发，最初是为了类 UNIX 平台而设计。现在可以支持各种主流的平台，例如 Linux、BSD、AIX、HP-UX、Mac OS X、Solaris 以及 Windows 等
  
• PostgreSQL 遵循事务的 ACID 原则（温习一下基本功： ACID 原则：即原子性、一致性、独立性、持久性），对 SQL 标准高度兼容，对于 PostgreSQL 13，至少符合 SQL:2016 核心一致性的 179 项强制功能中的 170 项；目前，还没有任何关系型数据库产品完全符合该标准
  
• 数据类型
  
  
  
  • 基本类型：整型、数值、字符串、布尔值
    
    
    
    • mysql本质上并不支持boolean类型，当我们创建了boolean的字段属性之后，mysql会自动将其转化为tinyint(1)类型。当插入“true”的时候，其值自动转化为1。当插入"false的时候，其值自动转化为0。使用select进行取值的时候，我们搜索出来的也是0和1。而pgsql是支持 boolean的，所以我们可以插入“true和“false”的类型。
      
    
    
  
  

CREATE TABLE Test_mysql (
t_id VARCHAR,
t_bool BOOLEAN
);

INSERT INTO Test_mysql
(t_id, t_bool)
VALUES
  ('t_1',false),
  ('t_2',true)

• PostgreSQL 是支持text类型，text类型用于保留无限长的字符,字段最大存储1GB
  


• 结构化类型：日期/时间、数组、范围、UUID
  
  
  
  • 一般来说，MySQL将数据合法性验证交给客户；PostgreSQL在合法性难方面做得比较严格。比如MySQL里插入 “2012-02-30” 这个时间时，会成功，但结果会是 “0000-00-00”；PostgreSQL不允许插入此值。
    
  
  

--当输入正常的日期
CREATE TABLE Test_mysql (
t_id VARCHAR,
t_date DATE
);

INSERT INTO Test_mysql
(t_id, t_date)
VALUES
  ('t_1','2022-06-08')


--输入不正常的日期

--mysql：
CREATE TABLE fiddle.Test_mysql (
t_id VARCHAR(20),
t_date DATE
);

INSERT INTO Test_mysql
(t_id, t_date)
VALUES
  ('t_1','0000-06-08');
  
select t_id, t_date from Test_mysql


--pgsql
CREATE TABLE Test_mysql (
t_id VARCHAR,
t_date DATE
);

INSERT INTO Test_mysql
(t_id, t_date)
VALUES
  ('t_1','0000-06-08')
mysql执行结果：


pgsql执行结果：

• 文档类型：JSON/JSONB、XML、键值存储（Hstore）
  
  
  
  • Hstore模块实现hstore数据类型用于在单值中存储键值对。hstore数据类型在很多场景中非常有用，如半结构化数据或有很多属性却很少被查询的行。注意键值对仅能为文本字符串
    
  
  

# 启用PostgreSQL hstore扩展
CREATE EXTENSION hstore;
​
# 创表
CREATE TABLE Movie (
Movie_id serial primary key,
Movie_name VARCHAR,
Movie_Attr hstore
);
# Movie_id         -  是用于查找电影的主键。
# Movie_name  -  是电影的其他名称
# Movie_attr      -  用于包含电影的属性，例如，评分，电影类型，语言，放映时间和发行年份。
# 对于 movie_attr 列，使用了hstore 数据类型
​
​
INSERT INTO Movie (Movie_name, Movie_attr)
VALUES
(  'Avengers Endgame',
  '"rating" => "8.4",
    "movie_genres" => "Action/Sci-fi",
    "language" => "English",
    "release_year" => "2019",
  "running_time" => "181 Minutes"'
),
  ( 'US',
  '"rating" => "6.9",
    "movie_genres" => "Horror/Thriller",
    "language"  => "English",
    "release_year" => "2019",
  "running_time"  => "116 Minutes"'
),
( 'Dolittle',
  '"rating" => "5.6",
    "movie_genres" => "Adventure/Family ",
    "language" => "English",
    "release_year" => "2020",
  "running_time" => "101 Minutes"'
);
​
#查询属性
SELECT
Movie_name, Movie_attr -> 'movie_genres' AS Genres
FROM Movie
WHERE Movie_attr -> 'release_year' = '2019';

        上述代码可筛选出根据Movie_attr中'release_year' = '2的电影名字和电影的类型属性

• 几何类型：点、线、圆、多边形
  
• 定制化类型：复合类型、自定义类型
  


• 数据完整性
  
  
  
  • NOT NULL：指示某列不能存储 NULL 值。
    
  • UNIQUE：确保某列的值都是唯一的。'
    
  • PRIMARY Key：NOT NULL 和 UNIQUE 的结合。
    
  • FOREIGN KEY 约束：指定列(或一组列)中的值必须匹配另一个表的某一行中出现的值。
    
  • CHECK 约束：约束保证列中的所有值满足某一条件 即对输入一条记录要进行检查。如果条件值为 false，则记录违反了约束，且不能输入到表。
    
  
  

-- CHECK SALARY 必须为正数
CREATE TABLE C1(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   SALARY         REAL    CHECK(SALARY > 0)
);

• 排他约束:确保如果使用指定的运算符在指定列或表达式上比较任意两行，至少其中一个运算符比较将返回 false 或 null
  

--安装 btree_gist 扩展，它是对纯标量数据类型的 EXCLUDE 约束
CREATE EXTENSION btree_gist
​
--建表
CREATE TABLE C2(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT,
   AGE            INT  ,
   EXCLUDE USING gist
   (NAME WITH =,  -- 如果满足 NAME 相同，AGE 不相同则不允许插入，否则允许插入
   AGE WITH <>)   -- 其比较的结果是如果整个表边式返回 true，则不允许插入，否则允许
);
​
--向表插入记录来查看这一点：
​
INSERT INTO C2 VALUES(1, 'Paul', 32);
INSERT INTO C2 VALUES(2, 'Paul', 32);
-- 此条数据的 NAME 与第一条相同，且 AGE 与第一条也相同，故满足插入条件
INSERT INTO C2 VALUES(3, 'Paul', 42);
-- 此数据与上面数据的 NAME 相同，但 AGE 不相同，故不允许插入
​
报错：
ERROR:  conflicting key value violates exclusion constraint "company7_name_age_excl"
DETAIL:  Key (name, age)=(Paul, 42) conflicts with existing key (name, age)=(Paul, 32).
​

• 可扩展性
  
  
  
  • PostgreSQL 提供了一种访问和操作外部数据源的机制，称为外部数据包装器（Foreign Data Wrapper）。利用这种外部数据管理机制，我们可以在 PostgreSQL 中访问各种同构数据库（PostgreSQL）、异构数据库（MySQL、Oracle、SQL Server、SQLite、HBase、Cassandra、ClickHouse、CouchDB、MongoDB、Neo4j、Redis、Hadoop、HIve等）以及文本文件（CSV、JSON、XML等）   比如 连接 MySQL 数据库的外部数据包装器：mysql_fdw。mysql_fdw 提供了读写 MySQL 外部表、连接池、WHERE 条件下推、返回字段下推、预编译语句、JOIN 下推、聚合函数（min、max、sum、avg、count）下推、ORDER BY 下推以及 LIMIT OFFSET 下推等功能。
    
  
  

-- 加载扩展插件
CREATE EXTENSION mysql_fdw;
​
-- 创建服务器对象
CREATE SERVER mysql_server
    FOREIGN DATA WRAPPER mysql_fdw
    OPTIONS (host '127.0.0.1', port '3306');
​
-- 创建用户映射
CREATE USER MAPPING FOR postgres
    SERVER mysql_server
    OPTIONS (username 'foo', password 'bar');
​
-- 创建外部表
CREATE FOREIGN TABLE warehouse
    (
        warehouse_id int,
        warehouse_name text,
        warehouse_created timestamp
    )
    SERVER mysql_server
    OPTIONS (dbname 'db', table_name 'warehouse');
​
-- 插入数据
INSERT INTO warehouse values (1, 'UPS', current_date);
INSERT INTO warehouse values (2, 'TV', current_date);
INSERT INTO warehouse values (3, 'Table', current_date);
​
-- 查询数据
SELECT * FROM warehouse ORDER BY 1;
​
warehouse_id | warehouse_name | warehouse_created
-------------+----------------+-------------------
           1 | UPS            | 10-JUL-20 00:00:00
           2 | TV             | 10-JUL-20 00:00:00
           3 | Table          | 10-JUL-20 00:00:00
​
-- 删除数据
DELETE FROM warehouse where warehouse_id = 3;

• 大量额外功能的扩展：在 PostgreSQL 中，用户可以定义自己的数据类型、索引类型、过程语言等等。市场上存在大量基于 PostgreSQL 的数据库产品，例如 Greenplum、EnterpriseDB、TimescaleDB、Citus 等等。无论是可管理的数据量还是支持的用户并发数，它都具有高度的可扩展性。
  
• 国际化、全文检索
  
  
  
  • 多种方式支持国际字符集，例如通过 ICU 排序规则
    
  • 使用ICU排序规则时，可以为比较和排序提供便利 假如我们现在需要筛选表tab中的col字段中的ABS,abs,Abs诸如此类的字段，有哪些筛选方法：
    
  
  

   常规：
   select  * from tab where lower(col) =lower('ABC'); ICU排序：
其中 ’en-US-u-ks-level2‘ 是针对美式英语的排序，如果是对德语等其他需要，排序规则见 https://www.cockroachlabs.com/docs/v20.1/collate.html
--定义排序规则
CREATE COLLATION english_ci (
​
   PROVIDER = icu,
​
   -- 'en-US@colStrength=secondary' for old ICU versions
​
   LOCALE = 'en-US-u-ks-level2',
​
   DETERMINISTIC = FALSE  -- 不确定排序
​
);
​
--定义表
CREATE TABLE tab (
​
   col text COLLATE english_ci,
​
   ...
​
);
​
--查询
​
SELECT id, col FROM tab
​
WHERE col = 'abc';
​

• 并发、性能（本期不做详细介绍）
  
  
  
  • 索引：B-tree、复合索引、函数索引、部分索引
    
  • 高级索引：GiST、SP-Gist、KNN Gist、GIN、BRIN、覆盖索引、布隆过滤器索引
    
  • 复杂的查询计划器/优化器、Index-Only 扫描、多列统计
    
  • 事务、嵌套事务（通过保存点实现）
    
  • 多版本并发控制（MVCC）
    
  • 并行查询以及 B-tree 索引的并行创建
    
  • 表分区
    
  • 表达式的即时（JIT）编译
    
  
  
• 可靠性、灾难恢复 （本期不做详细介绍）
  
  
  
  • 预写式日志（WAL）
    
  • 复制：异步复制、同步复制、逻辑复制
    
  • 基于时间点的恢复（PITR）、活动备份
    
  • 表空间
    
  
  
• 安全 （本期不做详细介绍）
  
  
  
  • 身份认证：GSSAPI、SSPI、LDAP、SCRAM-SHA-256、SSL 证书等等
    
  • 强大的访问控制系统
    
  • 列级与行级安全性
    
  
  

3.Python连接pgsql: psycopg2

Python 定义了连接和操作数据库的标准接口 Python DB API。不同的数据库在此基础上实现了特定的驱动，这些驱动都实现了标准接口。



图片引用自https://www.zhihu.com/column/c_1452567507496689664

psycopg2，是Python语言的PostgreSQL数据库接口，是对Psycopg 1.1.x版本进行的几乎完全的改写,
1.安装：pip3 install psycopg2
2.连接：
# psycopy2提供了一个cursor类，用来在数据库Session里执行PostgresSQL命令。
import psycopg2
​
conn = psycopg2.connect(database="dastudiodb", user="用户名", password="密码", host="172.xx.xx.xx", port="54xx")
cur = conn.cursor()
cur.execute("SELECT id from da.da_combine_model")
rows = cur.fetchall()
​
for row in  rows:
    print(row)3.基础操作
# 创建新表
CREATE TABLE IF NOT EXISTS dictionary(english VARCHAR(30), chinese VARCHAR(80), times SMALLINT, in_new_words SMALLINT);
# 插入数据
INSERT INTO dictionary(english, chinese, times, in_new_words) VALUES('hello', '你好', 0, 0);
# 选择记录
SELECT * FROM dictionary WHERE english = 'hello';
# 更新数据
UPDATE dictionary SET times = 1 WHERE english = 'hello';
# 删除记录
DELETE FROM dictionary WHERE english = 'hello';4.关闭：注 当操作postgreSQL 时，当连接关闭后，程序对数据库的操作才会生效。所以放在每一次操作的结束时需要执行。
# 提交事务
conn.commit()
#关闭连接
conn.close()不懂就问：psycong2与psycong2-binary的区别在哪？？
4. 终端工具DBeaver

dbeaver是一个通用的数据库管理工具和 SQL 客户端，支持 MySQL, PostgreSQL, Oracle, DB2, MSSQL, Sybase, Mimer, HSQLDB, Derby, 以及其他兼容 JDBC 的数据库。DBeaver 提供一个图形界面用来查看数据库结构、执行SQL查询和脚本，浏览和导出数据，处理BLOB/CLOB 数据，修改数据库结构等等。
支持的操作系统：Windows /  Linux / Mac OS  等
下载地址：https://dbeaver.io/download/
5.篇未总结

基于上述几个方面，开发大哥嘴中所说的“pgsql是最先进的数据库”确有此事，不过还有个疑问，pgsql既然这么牛，为什么知名度没有mysql高呢，再去查看，与“pgsql是最先进的数据库”相比还有一句话--“mysql是最受欢迎的数据库”
主要因为：

• MySQL使用起来更简单，在Windows平台上安装比较容易。早期的PostgreSQL没有提供Windows平台的版本
  
• 学习MySQL更加容易， 开箱即用，以root用户连接非常简单， 但是配置PostgreSQL、创建用户等等操作比MySQL要复杂
  
• MySQL的社区和配套产品的生态系统，无论是在线文档，还是论坛都比Postgre SQL丰富
  

总结一句，很多时候，我们对于数据库的需求相当于是“杀鸡”，而相比mysql，Postgre SQL 更像是一把“牛刀”。

浩瀚无穷

膜拜大佬