MySQL与PostgreSQL语法比较

PostgreSQL（pgsql）

插入冲突则更新

#Mysql

insert into distributors (did,dname)values (5,'Gizmo'),(6,''INc) 
ON DUPLICATE KEY UPDATE dname = EXCLUDED.dname ;

#pgsql

insert into distributors (did,dname)values (5,'Gizmo'),(6,''INc) 
ON CONFLICT (did) DO UPDATE SET dname = EXCLUDED.dname ;

插入冲突则什么都不做

#mysql

INSERT IGNORE INTO distributors (did,dname) VALUES (9,'Design'):

#pgsql

INSERT INTO distributors (did,dname) VALUES (9,'Design') ON CONFLICT ON 
CONSTRAINT distributors_pkey DO NOTHING;

索引类型

#MySQL

btree索引

#pgsql

支持多种索引类型：btree hash gin gist sp-gist bloom rum brin
还支持exclude索引、表达式索引、partial索引（分区索引）

约束

#MySQL

都支持主键约束、外键约束、唯一约束、not null约束等

#pgsql

都支持主键约束、外键约束、唯一约束、not null约束等

ID自增

#MySQL

设置主键ID自增

#pgsql

create table infisa_tmplate_config(id serial);
--设置序列从1开始，自增1
create SEQUENCE user_id_seq START WITH 1
INCREMENT BY 1 ON MINVALUE ON MAXVALUE CACHE 1;
--设置席列
ALTER table user ALTER column id SET DEFAULT nextval('user_id_seq'):

符号区别

#MySQL

反号引号（tab键上方的键）

#pgsql

"" 双引号是区分库名，关键字等

时间转字符串

#MySQL

date_format(a.tag_create_date, '%Y-%m-%d %H:%i:%s')

#pgsql

to_char(a.tag_create_date, 'yyyy-mm-dd HH:M:SS')

字符串转时间

#MySQL

date_format(a.tag_create_date,'%Y-%m-%d %H:%i:%s')

#pgsql

to_date(a.tag_create_date, 'yyyy-mm-dd HH:M:SS')

IFNULL()函数

#mysql

IFNULL(a.id,'')

#pgsql

COALESCE(a.id,'')

find_in_set()函数

#MySQL

SELECT t.dept_id FROM sys_dept t WHERE find_in_set('100', ancestors)

#pgsql


SELECT t.dept_id FROM sys_dept t WHERE '100' = ANY (string_to_array(ancestors,''))

group_concat()函数

#MySQL

select a.name,group_concat(distinct city) from user_city a group by a.name;

#pgsql

select a.name, array_to_string(array_agg(distinct a.city), ',' ) from user_city a group by a.name;

offset/limit

#mysql

select * from tl limit 2,2;

#pgsql

select * from t1 0FFSET 2,2;

DISTINCT

#mysql

select DISTINCT b.id from hospital.ods_user_basic as b

#pgsql

select DISTINCT ON (b.id) b.* from hospital.ods_user_basic as b

递归函数

#MySQL

with RECURSIVE cte as
(
    select a.id, a.name from tb a where id = '002'
    union all 
    select k.id, k.name from tb k inner join cte c on c.id = k.pid
)
select id, name from cte;

#pgsql

with recursive p as
(
    select tl.* from t_org_test t1 WHERE t1.id = 2
    union al
    select t2.* from t_org_test t2 inner join p on t2.parent_id = p.id
)
select id,name,parent_id from p

分区

#mysql

CREATE TABLE 'animal'
(
id bigint NOT NULL AUTO_INCREMENT COMMENT '主键ID:自增字段',
read_time varchar(20) NOT NULL COMMENT '采集时间(yyyy-mm)',
org_id bigint DEFAULT NULL COMMENT '组织',
use_water DECIMAL(24,8) DEFAULT NULL COMMENT '正累计用水量',
plus_use_water DECIMAL(24,8) DEFAULT NULL COMMENT '净累计用水量',
meter_num int DEFAULT NULL COMMENT '水表数量',
is_interpolate int DEFAULT 'O' COMMENT '是否差补创建时间',
create_time datetime(3) DEFAULT NULL COMMENT,
update_time datetime(3) DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY ('id','read_time'),
UNIQUE KEY 'unig_read_time' COMMENT 'read_time,org id索引'
)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_aici COMENT='区域用水量月报表'
/*!50500 PARTITION BY RANGE COLUMNS(read_time)
(PARTITION P2021 VALUES LESS THAN (2022-01’) ENGINE = InnoDB,
PARTITION p2022 VALUES LESS THAN 2023-01') ENGINE = InnoDB) */


#pgsql

1、创建需要分区的主表
CREATE TABLE"test01"."animal"
(
"id" varchar NOT NULL,
"name" varchar(255)，
"age" int4,
"create_time" date,
"update_time" date
) partition by range (create_time);
2、创建分区
CREATE TABLE animal 2022 PARTITION of animal for VALUES from ( '2021 01 01') to ( '2022 01 01');
CREATE TABLE aninal_2023 PARTITION of animal for VALUES from ( '2022_01_01') to ('2023_01_01');

partition by

#mysql 

select id,name,class_id,score,lag(score,1,0) over (partition by class_id order by score desc) before_score from t_student;

#pgsql

select
country,
language_id.
row_number() over(partition by country) as rn,
row_number() over() as rownun
from
course;

跨库查询

#MySQL

跨库查询比较方便，权限分配好，直接通过dbname.tablename就可以访问

#pgsql

跨库查询复杂：
1.shchema(架构方式)
2.dblink (密码写在链接字符串上，不安全)
3.postgres fdw是一种外部访问接口

多表更新

#MySQL

update employees,accounts set sales_count=sales_count+1
where accounts.name = 'aa' and employees.id = accounts.sales_persion

#pgsql

update employees set sales_count+1 from accounts where accounts.name='aa'
and employees.id = accounts.sales_person