关于PostgreSql数据库和mysql的不同点及注意事项

关于PostgreSql数据库和mysql的不同点及注意事项

一、数据类型：

1. update time （更新时间）

更新时间戳需要通过触发器来实现。

1. 定义触发器

CREATEORREPLACEFUNCTION"public"."cs_timestamp"()RETURNS"pg_catalog"."trigger"AS $BODY$
begin
    new.updated_time=current_timestamp;return new;end
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100

1. 创建触发器

createtrigger cs_time before updateon table1 for each rowexecuteprocedure 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是一个组合词，即当往表中插入记录，如果该记录已存在则更新，否则插入新记录。为了使用该特性需要使用

INSERTON CONFLICT：
INSERTINTO 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 表示如果数据相同则不做任何操作。例如

INSERTINTO customers (name, email)VALUES('Microsoft','[email protected]')ON CONFLICT (name)DO NOTHING;

1. DO UPDATE 则表示如果存在以 name为唯一索引的重复数据,则做更新操作。 例如：

INSERTINTO customers (name, email)VALUES('Microsoft','[email protected]')ON CONFLICT (name)DOUPDATESET email = EXCLUDED.email ||';'|| customers.email;

其中EXCLUDED为虚拟表，EXCLUDED中包含我们药更新的记录，也就是新记录，而customers为原纪录。

4. Boolean

boolean 有"true"或"false"两个状态，第三种"unknown"(未知)状态，用 NULL 表示;
PostgreSQL支持TRUE和FALSE的以下有效文字值。除了 True 和 False 之外的所有常量值都必须用单引号引起来
TrueFalsetruefalse‘t’'F ’‘true’‘false’‘yes’‘no’‘1’‘0’
例如：

INSERTINTOtable(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

1. 精确型：decimal、numeric
2. 不精确型：real、double precision（float等价于double precision）

二、Postgresql函数与mysql异同点：

1. if函数

不支持if函数，可改为case when语句;

2. Ifnull

改为COALESCE()
例如：

COALESCE(a.result, '')

3. 时间函数

1. 没有datediff，使用 date_part date_part(‘epoch’, TIMESTAMP ‘2019-05-05 12:11:20’ - TIMESTAMP ‘2019-05-05 10:10:10’)
2. 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 子句提供类似功能,

DELETEFROM 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

三、数据库编码

1. sql 中表名和字段名不要用双引号！
2. 所有schema（数据库名，模式名，表名，字段名，字段类型等）定义必须小写
3. 字段前面不能用波浪线 ‘~ ’
4. postgresql模式，一版用public，缺省用public。