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