关于PostgreSql数据库与mysql数据库的不同点以及注意事项，
目录
- 一、数据类型：
- 1. update time （更新时间）
- 2. IN
- 3. upsert
- 4. Boolean
- 5. datetime
- 6. decimal 精确型：
- 二、Postgresql函数与mysql异同点：
- 1. if函数
- 2. Ifnull
- 3. 时间函数
- 4. dml schema不一样
- 5. group by 聚合函数和mysql不同
- 6. update join 语法不一致
- 7. delete join语法不一致
- 8. 模糊匹配
- 9. 支持retuning子句
- 三、数据库编码
- 总结
一、数据类型：
1. update time （更新时间）
更新时间戳需要通过触发器来实现。
定义触发器
CREATE OR REPLACE FUNCTION "public"."cs_timestamp"()
RETURNS "pg_catalog"."trigger" AS $BODY$
begin
new.updated_time= current_timestamp;
return new;
end
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100
创建触发器
create trigger cs_time before update on table1 for each row execute procedure cs_timestamp();
2. IN
dapper 判断了connection 是否是npgsql ，如果是，表示支持 数组类型，不展开数组。也就是 in @ids ,在npgsql下不做转化为 in (@p1,@p2…),故 在pgsql 下要用 any(@ids)
例如：
select * from tbl where id = any(@ids);
3. upsert
upsert是一个组合词，即当往表中插入记录，如果该记录已存在则更新，否则插入新记录。为了使用该特性需要使用
INSERT ON CONFLICT： INSERT INTO table_name(column_list) VALUES(value_list) ON CONFLICT target action;
target 可以是：
(column_name)：一个字段名
ON CONSTRAINT constraint_name：其中的 constraint_name 可以是一个唯一约束的名字
WHERE predicate： WHERE 子句
action为下面两者之一：
1.DO NOTHING 表示如果数据相同则不做任何操作。例如
INSERT INTO customers (name, email)
VALUES('Microsoft','hotline@microsoft.com')
ON CONFLICT (name)
DO NOTHING;
2.DO UPDATE 则表示如果存在以 name为唯一索引的重复数据,则做更新操作。
例如：
INSERT INTO customers (name, email)
VALUES('Microsoft','hotline@microsoft.com')
ON CONFLICT (name)
DO
UPDATE SET email = EXCLUDED.email || ';' || customers.email;
其中EXCLUDED为虚拟表，EXCLUDED中包含我们药更新的记录，也就是新记录，而customers为原纪录。
4. Boolean
boolean 有"true"或"false"两个状态，第三种"unknown"(未知)状态，用 NULL 表示;
PostgreSQL支持TRUE和FALSE的以下有效文字值。除了 True 和 False 之外的所有常量值都必须用单引号引起来
| True | False |
|---|---|
| true | false |
| ‘t’ | 'F ’ |
| ‘true’ | ‘false’ |
| ‘yes’ | ‘no’ |
| ‘1’ | ‘0’ |
例如：
INSERT INTO table (id,status ) VALUES (101, TRUE), (201, FALSE), (301, 't'), (401, '1'), (501, 'y'), (601, 'yes'), (701, 'no'), (801, '0');
5. datetime
PostgreSql中没有datetime类型，主要有：TIME、DATE、TIMESTAMP、INTERVAL。日期时间类型输入像字符串类型输入一样，需要加单引号。每种日期时间类型都有合法的取值范围，超出范围时系统会将“零”插入数据记录中。
TIMESTAMP数据类型分为 两种时间数据类型，如下:
Timestamp: : 使用时间戳数据类型 ，不带时区。
Timestamptz: : timestamptz数据类型用于带有时区的 。
6. decimal 精确型：
- 精确型：decimal、numeric
- 不精确型：real、double precision（float等价于double precision）
二、Postgresql函数与mysql异同点：
1. if函数
不支持if函数，可改为case when语句;
2. Ifnull
改为COALESCE()
例如：COALESCE(a.result, '')
3. 时间函数
- 没有datediff，使用 date_part
date_part(‘epoch’, TIMESTAMP ‘2019-05-05 12:11:20’ - TIMESTAMP ‘2019-05-05 10:10:10’) - date_add替换 now() + interval ‘2 year’;
4. dml schema不一样
5. group by 聚合函数和mysql不同
postgresql输出中的所有字段（包含聚合函数的计算字段除外）必须存在于GROUP BY子句中。
6. update join 语法不一致
UPDATE t1 SET t1.c1 = new_value FROM t2 WHERE t1.c2 = t2.c2;
7. delete join语法不一致
PostgreSQL不支持 Delete join语句，但支持delete中using 子句提供类似功能,
DELETE FROM t1 USING t2 WHERE t1.id = t2.id
使用using指定删除t1 中在 t2中的数据
8. 模糊匹配
like 改为 ilike
ilike '%'|| #{testItem} ||'%' 或 like concat('%', #{testItem}, '%')
9. 支持retuning子句
insert returning 返回的是新插入的值。
delete returning 返回的是被删除的值。
update returning 返回的是更新后的值，不能返回更新前的值。
例如：
UPDATE customers SET email = '123' where name='Microsoft' RETURNING email
三、数据库编码
- sql 中表名和字段名不要用双引号！
- 所有schema（数据库名，模式名，表名，字段名，字段类型等）定义必须小写
- 字段前面不能用波浪线 ‘~ ’
- postgresql模式，一版用public，缺省用public。
总结
到此这篇关于PostgreSql数据库与mysql数据库的不同点以及注意事项的文章就介绍到这了,更多相关PostgreSql与mysql的不同点内容请搜索PHP之友以前的文章或继续浏览下面的相关文章希望大家以后多多支持PHP之友！
您可能感兴趣的文章:- 基于PostgreSQL和mysql数据类型对比兼容
- Mysql迁移Postgresql的实现示例
- PostgreSQL与MySQL优劣势比较浅谈