从MySQL切换到PostgreSQL-提示，技巧和陷阱? [英] Switching from MySQL to PostgreSQL - tips, tricks and gotchas?

问题描述

我正在考虑从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迁移到PostgreSQL

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 IGNORE和REPLACE
• 几乎所有地方都需要显式投射
• 没有反引号
• LOAD DATA INFILE(COPY距离近，但距离不够远)
• 将autoincrement更改为SERIAL
• 尽管在MySQL中格式很差，但在Postgres中，没有ON子句的INNER JOIN不会发生，请使用CROSS JOIN或类似的方法
• COUNT(*)可能会发疯慢行
• 数据库使用字符集而不是表进行编码
• 您可以拥有具有多个架构的多个数据库(MySQL实际上只有一个数据库和多个架构)
• 分区不同
• MySQL interval与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();.

这篇关于从MySQL切换到PostgreSQL-提示，技巧和陷阱?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！