mysql适配postgresql语法差异总结

安装pgsql就不说了，网上教程很多，这边主要阐述下在适配过程中所遇到的语法差异

1.postgresql对于类型比较很严格

int和varchar的比较，需要将int型的字段强转为varchar
强转符号 ::varchar
例：
LEFT JOIN test lic ON lic.ID::varchar = lr.NAME

2.注意从mysql直接转换过来的表，自增的未同步过来

DROP SEQUENCE IF EXISTS RELATION_id_seq;
CREATE SEQUENCE RELATION_id_seq START 1;
alter table relation ALTER COLUMN id set DEFAULT nextval(‘RELATION_id_seq’);
初始脚本执行，不能自己赋值自增的主键，否则实际使用时易造成主键冲突
例：
INSERT INTO “public”.“role” VALUES (1,‘超级管理员’, 0, ‘超级管理员’, 0);
改为
INSERT INTO “public”.“role” (rolename, seq, description, status) VALUES (‘超级管理员’, 0, ‘超级管理员’, 0);

3.bool类型 mysql传的1和0 postgresql只能传true和false

将字段类型改为smallint

4.replace into 语法不支持

改为
insert into () values ()
on conflict(主键) do update
set key=value
例：
replace into test
set
userid = #{userid}, type = #{type}, id = #{id}

改为
insert into test
(userid, type, id)
values
(#{userid},${type}, ${id})
on conflict(id) do update
set userid = #{userid}, type = ${type}

5.多表关联删除不支持，除非用外键

DELETE e, ea, e2t
FROM fence e
LEFT JOIN fence_area ea ON ea.FENCE_ID = e.ID
LEFT JOIN fence2tag e2t ON e2t.id = e.ID
WHERE e.ID = ‘123’

需要拆分开来，一个表一个表删

如需关联其他表的字段，可以使用using关联
DELETE FROM fence e USING fence_area ea, fence2tag e2t
WHERE ea.FENCE_ID = e.ID and e2t.id = e.ID and e.ID = ‘38742893727’

或者使用using并且两张表的关联外键的删除时动作改为CASCADE (fence2tag目前还没有设置外键)

6.分页改为LIMIT ${num} OFFSET ${start}

例：limit 0,1 改为 limit 1 offset 0

7.更新表时关联表，不能用left join

例：
update showset a left join tag b on b.groupid=#{id} and a.id=b.id set a.marsize=#{marsize}, a.markerclr=#{markerclr},a.textsize=#{textsize},a.textclr=#{textclr}
改为
update showset
set marsize=#{marsize}, markerclr=#{markerclr},textsize=#{textsize},textclr=#{textclr}
from showset a
left join tag b on b.groupid=#{id} and a.id=b.id

8.插入的时候mybatis预置参数得将int类型用$号而不能用#

当字段为数字类型，字段查询比较建议使用where tagid = KaTeX parse error: Expected 'EOF', got '#' at position 40: …如果tagid是数字型，在使用#̲号的情况下，java传入字符型…就不会

9.针对单个类型的参数 在dao中的表现形式为 String id

则需要改为 @Param(“id”) String id
不然sql中会识别不到传入的参数
例：
void deleteMess(String markid);
改为
void deleteMess(@Param(“markid”)String markid);
注意该名称需要和sql中使用的变量名一致

10.更新表时不能使用别名

例如 update tag lt set lt.id=… 这样会报错
应该用 update tag set id=…

11.mysql if(a,b,c) 三元判断符，pgsql不支持需要自建函数

create or replace function if(bln boolean, inValue1 anyelement, inValue2 anyelement)
returns anyelement as
$$
begin
if bln=true then
  return inValue1;
else
  return inValue2;
end if;
end;
$$
language plpgsql;

12.字段别名不能使用双引号

解决方案：不加或使用单引号

13.保留字，例如user，如果数据库中有字段名称是它，则必须要加双引号