sqlpostgresql_0">SQL语句Mysql向postgresql迁移笔记
最近改用国产数据库,sql>mysql下的sql语句在在postgresql上执行报错,初步修改。
文章目录
char类型查出来后面有空格
程序删除,或者修改为varchar类型
双引号的使用
sql>mysql_7">sql>mysql特有符号“`”需要改为双引号
SELECT `id`,`name`,`user`,`pwd` from `users`;
需要修改为
SELECT "id","name","user","pwd" from "users";
sql_17">postgresql关键字必须加双引号
SELECT id,name,user,pwd from users;
需要修改为
SELECT id,name,"user",pwd from users;
数据库名称,表名,列名等如果有大写字符,必须双引号
SELECT “ID”,“Name”,“User”,“Pwd” from “Users”;
连表更新语句
UPDATE `custom`,`channels`
SET `custom`.`status` = `channels`.`Status`
WHERE `custom`.`id` = `channels`.`id`;
postgresql语法
UPDATE "custom"
SET "custom"."status" = "channels"."Status"
FROM "channels"
WHERE "custom"."id" = "channels"."id";
时间函数
UNIX_TIMESTAMP 时间转时间戳
UNIX_TIMESTAMP(`start`) => TRUNC(EXTRACT(EPOCH FROM "start"))
FROM_UNIXTIME 时间戳转时间
FROM_UNIXTIME(时间戳) => TO_TIMESTAMP(时间戳)
DATA_ADD 时间戳加减
DATA_ADD(now() INTERVAL 180 SECOND) => now() + interval '180 S'
DATA_SUB(now() INTERVAL 180 SECOND) => now() - interval '180 S'
排序删除
DELETE FROM `gb_alarms` WHERE `created_at` < DATE_SUB(CURDATE(),INTERVAL 90 DAY) ORDER BY `created_at` ASC LIMIT 1000;
postgresql语法
DELETE FROM "gb_alarms" WHERE "id" IN ( SELECT "id" FROM "gb_alarms" WHERE created_at < now( ) - INTERVAL '90 D' ORDER BY created_at ASC LIMIT 1000 );
VARCHAR 不允许默认空字符串,需要需要非空索引。
不知道为什么,无法配置postgresql的列默认值为空字符串,只能修改表结构,但是基本不影响。