MySQL迁移至PostgreSQL过程记录

最近项目需要在MySQL数据库的基础上，增加对PostgreSQL的支持，由于对PostgreSQL不了解，导致遇到非常多的坑，故在这里记录一下

---

不存在就插入，存在就更新

MySQL:

REPLACE INTO tablename (`a`, `b`, `c`) VALUES (1, 2, 3);

PostgreSQL:
不存在MySQL的REPLACE INTO，但在9.5版本后新增upsert操作，类似REPLACE INTO

INSERT INTO "tablename" ("a", "b", "c") values ('1', '2', '3')
ON CONFLICT ("a") DO UPDATE SET "a" = '1', "b" = '2', "c" = '3' WHERE "tablename"."a" = '1';

参考: https://stackoverflow.com/a/30118648/7151777
https://stackoverflow.com/a/17267423/7151777

---

单引号、双引号、反单引号

MySQL:
允许使用单引号或双引号扩住值，且使用反单引号扩住表名字段名是个好习惯

PostgreSQL:
建议使用双引号扩住表名字段名，建议使用单引号扩住值，不能使用反单引号

参考: https://blog.csdn.net/Koprvhdix/article/details/83536427
https://blog.csdn.net/u013494765/article/details/87811809

---

查找以abc开头的所有表

MySQL:

SHOW TABLES LIKE 'abc%';

PostgreSQL:

SELECT "table_name" FROM "information_schema"."tables" WHERE "table_name" LIKE 'abc%';

参考：https://blog.csdn.net/u011402596/article/details/38510547?utm_medium=distribute.pc_relevant.none-task-blog-2%7Edefault%7EBlogCommendFromMachineLearnPai2%7Edefault-12.control&depth_1-utm_source=distribute.pc_relevant.none-task-blog-2%7Edefault%7EBlogCommendFromMachineLearnPai2%7Edefault-12.control

---

数据库结构

MySQL:
数据库-表

PostgreSQL:
数据库-模式(schema)-表
默认的schema为public

---

数据类型

MySQL:
TINYINT，INT，BIGINT

PostgreSQL:
INT2，INT4，INT8

---

DDL

MySQL:

CREATE TABLE `main` (
  `data_id` varchar(128) NOT NULL COMMENT '数据id',
  `sync_datetime` timestamp NULL DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`data_id`),
  UNIQUE KEY `data_id` (`data_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='main';

PostgreSQL:

CREATE TABLE "schema_name"."main" (
  "data_id" varchar(128) COLLATE "pg_catalog"."default" NOT NULL,
  "sync_datetime" timestamp(6),
  CONSTRAINT "main_pkey" PRIMARY KEY ("data_id")
)
;

ALTER TABLE "schema_name"."main" 
  OWNER TO "postgres";

CREATE UNIQUE INDEX "main_data_id" ON "schema_name"."main" USING btree (
  "data_id" COLLATE "pg_catalog"."default" "pg_catalog"."text_ops" ASC NULLS LAST
);

COMMENT ON COLUMN "schema_name"."main"."data_id" IS '数据id';

COMMENT ON COLUMN "schema_name"."main"."sync_datetime" IS '更新时间';

COMMENT ON TABLE "schema_name"."main" IS 'main';