从 MySQL 切换到 PostgreSQL - 提示、技巧和陷阱?

我正在考虑从 MySQL 切换到 PostgreSQL.

I am contemplating a switch from MySQL to PostgreSQL.

您在使用 PostgreSQL 时有哪些提示、技巧和注意事项?

What are your tips, tricks and gotchas for working with PostgreSQL?

MySQLer 应该注意什么?

What should a MySQLer look out for?

另请参阅:PostgreSQL 与 MySQL 有何不同?
另请参阅:从 MySQL 迁移到 PostgreSQLp>

See also: How different is PostgreSQL to MySQL?
See also: Migrate from MySQL to PostgreSQL

注意 - 我不认为这是重复的.特别是答案的类型非常不同,这里的响应有更多的实现细节,这正是我正在寻找的

推荐答案

我自己刚刚经历过,嗯,我还是……

Just went through this myself, well I still am...

  • 区分大小写的文字
  • 缺少INSERT IGNOREREPLACE
  • 几乎所有地方都需要显式转换
  • 没有反引号
  • LOAD DATA INFILE(COPY 已接近,但还不够接近)
  • autoincrement 更改为 SERIAL
  • 虽然 MySQL 中的格式不好,但在 Postgres 中,不会发生没有 ON 子句的 INNER JOIN,请使用 CROSS JOIN 或喜欢
  • COUNT(*) 可能会非常慢
  • 数据库是用字符集编码的,而不是表
  • 您可以拥有多个数据库,具有多个架构(MySQL 确实只有一个数据库和多个架构)
  • 分区不同
  • MySQL interval vs. Postgres interval(时间间隔)
  • 隐式列重命名,Postgres 需要 AS
  • 不能在 Postgres 中同时更新多个表
  • Postgres 功能强大.所以没有 CALL proc();;将 proc() 重写为函数和 SELECT proc();.
  • Case sensitive text
  • Lack of INSERT IGNORE and REPLACE
  • Explicit casting needed almost everywhere
  • No backticks
  • LOAD DATA INFILE (COPY is close, but not close enough)
  • Change autoincrement to SERIAL
  • Although bad form in MySQL, in Postgres, an INNER JOIN without an ON clause can't happen, use CROSS JOIN or the like
  • COUNT(*) can be crazy slow
  • Databases are encoded with character sets, not tables
  • You can have multiple databases, with multiple schemas (MySQL really has just one database and multiple schema)
  • Partitioning is different
  • MySQL interval vs. Postgres interval (for time intervals)
  • Implicit column renaming, Postgres requires AS
  • Cannot update multiple tables at the same time in Postgres
  • Postgres functions are powerful. So there is no CALL proc();; rewrite proc() as a function and SELECT proc();.

相关文章