PostgreSQL备忘笔记

什么是PostgreSQL

PostgreSQL 是一个免费的对象-关系数据库服务器(ORDBMS)，完全开源，集关系与非关系于一身。由于前身为加州大学伯克利分校计算机系的学术研究系统，用于商业的时间比较晚，所有国内社区还不是很丰富。

数据类型

大部分类型与其他SQL差不了太多，主要讲解一下text，json这两个类型，其他类型可以通过下面的官方文档链接进行查看。

text：与其他数据库不同，pgsql中的text类型没有长度限制；json：其中有分为json和jsonb，json会输出和输入完全相同的文本，而jsonb 则不会保留语义上没有意义的细节（例如空格）。

下面这个是试玩Pgsql的json类型的实例

-- 建立t_user CREATE TABLE t_user( id int4 PRIMARY KEY NOT NULL, name text NOT NULL, age int4 NOT NULL, address char(50), salary float4, note jsonb ); INSERT INTO t_user(ID, NAME, AGE, ADDRESS, SALARY) VALUES(1, '张三', 18, '深圳市', 1001); UPDATE t_user SET note = '{"id":1, "name":"张三", "age":18, "address":"深圳市", "salary":1001, "note":[{"aa":11, "bb":22},{"aa":33, "bb":44}]}'; -- JSON中年龄等于18 SELECT * FROM t_user WHERE note @> '{"age":18}'; -- JSON中姓名包含“三” SELECT * FROM t_user WHERE note ->> 'name' LIKE '%三%'; -- JSON中姓名为张三或者李四 SELECT * FROM t_user WHERE note ->> 'name' IN ('张三', '李四'); -- 通过键获取JSON对象值 SELECT note ->> 'name' FROM t_user; -- 指定路径获取JSON对象 SELECT note #>> '{note, 0, aa}' FROM t_user; -- 建立t_note CREATE TABLE t_note ( id int4 NOT NULL, name varchar(255), aa int4 ); INSERT INTO t_note VALUES (1, '张三', 11); -- JSON连表查询 SELECT * FROM t_user u INNER JOIN t_note n ON u.note ->> 'name' = n.name; SELECT * FROM t_user u INNER JOIN t_note n ON u.note #>> '{note, 0, aa}' = concat('', n.aa, ''); -- 查询JSON中年龄大于10 SELECT * FROM t_user WHERE note @? '$.age[*] ? (@ >10)'; SELECT * FROM t_user WHERE note @@ '$.age[*] > 10'; -- 查询JSON中note数组的第一个值 SELECT note #>> '{note, 0}' FROM t_user ; -- 查询JSON中note数组的第一个值大于10 SELECT * FROM t_user WHERE CAST(note #>> '{note, 0}' as jsonb) @@ '$.aa[*] > 10';

数据类型官方文档：http://www.postgres.cn/docs/12/datatype.html JSON 操作官方文档：http://www.postgres.cn/docs/12/functions-json.html

语法

1、模式（SCHEMA）

模式可以看着是一个表的集合，一个模式可以包含表、视图、索引、函数和操作符等。相同的对象名称可以被用于不同的模式中而不会出现冲突。

使用模式的优势：将数据库对象组织成逻辑组以便更容易管理（如：每个微服务一个模式）

2、Pgsql的大小写敏感问题

字段名为小写：不区分大小写，不管是大小写都一律转为小写字段名为大写：区分大小写，并且需要加上双引号才能识别 小写实例： 大写实例： 所有推荐用小写来建立数据库、表和字段。

3、dblink

Pgsql不支持直接跨数据库查询，只有使用dblink来跨库连接。

-- 创建dblink create extension if not exists dblink; -- dblink 查询语句，后面需要声明返回值类型 -- host：数据库ip; dbname：数据库名称; user：用户名; password：密码 select * from dblink('host=192.168.1.105 dbname=testdb user=zxh password=123456', 'select department_id,department_coding,department_name from department') as t_temp(department_id text, department_coding text, department_name text) /**删除dblink**/ DROP extension dblink

在要使用dblink的数据库使用安装命令，安装完成后可以看到函数里面多了很多函数。

4、类型转换

由PostgreSQL进行的隐式类型转换会对查询的结果产生影响，必要时这些结果可以被使用显式类型转换来调整。

-------------------------------- gender_code 为 varchar 类型 -------------------------------- -- 执行成功 SELECT * FROM t_patient_info WHERE gender_code = '1'; -- 执行失败：没有匹配指定名称和参数类型的操作符. 您也许需要增加明确的类型转换. SELECT * FROM t_patient_info WHERE gender_code = 1; -- 类型转换示例1 SELECT * FROM t_patient_info WHERE gender_code :: INT = 1; -- 类型转换示例2 SELECT * FROM t_patient_info WHERE CAST(gender_code AS INT) = 1;

但是如果把上面的两句查询放到Oracle中，都能执行成功，Oracle会帮我们自动转换类型比较。

5、继承和重载

表：继承函数：重载

1）表继承示例

-- 创建城市表(name:名称; population:人口; altitude:海拔) CREATE TABLE cities ( name text, population float, altitude int ); -- 创建城市扩展表继承城市表(state:省份名称) CREATE TABLE capitals ( state text ) INHERITS (cities); -- 新增城市扩展表数据 INSERT INTO capitals VALUES('深圳市', 4000000, 50, '广东省'); INSERT INTO capitals VALUES('杭州市', 7000000, 20, '浙江省'); INSERT INTO capitals VALUES('广州市', 6000000, 40, '广东省'); -- 城市扩展表有数据 SELECT * FROM capitals; -- 城市表也有数据(父表和子表的数据都会查出来) SELECT * FROM cities; -- 新增城市表,城市扩展表没有新增数据 INSERT INTO cities VALUES('北京市', 4000000, 50); -- 只查父表数据 SELECT * FROM ONLY cities;

继承官方文档：http://www.postgres.cn/docs/12/ddl-inherit.html

2）函数重载（以dblink函数为例） 英文官方网站：https://www.postgresql.org/ 中文官方网站：http://www.postgres.cn/v2/home