Moving from MySQL to PostgreSQL

Using MySQL, MSSQL  for a long time and moving to PostgreSQL would be confusing and tiresome process for some of the developers, as there are some commonly used queries, practices are different in Postgres. Some of these changes seem strange and take sometime to find the existing queries, understand and fix them to make it work again.

Here I’ve compiled some of the changes to get started in fixing the Queries and write new Queries.

MySQL PostgreSQL
Comment Uses Non standard # to begin a comment Line PostgreSQL use ‘–‘ (double dash), as this is the ANSI standard, and both databases understand it.
Quotes Uses ‘ or ” to quote values. in WHERE clause you can use both. This is not ANSI standard for db uses only single quotes for this. in WHERE clause you can only use ‘ single quotes. Double quotes are used to quote system identifiers; field names, table names, etc.
accent/Backtick uses ` (accent mark or backtick) to quote system identifiers This is non standard. so not used/allowed
String Comparison string comparisons are case-insensitive string comparisons are case-sensitive

  • Need to use correct case in queries
  • Need to use case conversion function to search
  • To search / compare case-insensitive use operator, like ILIKE or ~*
Database, table, field and columns names Database, table, field and columns names can be case-sensitive or not, depending on which OS you are using Database, table, field and columns names are case-independent, unless they are created with double-quotes around their name, in which case they are case-sensitive.
Date Handling Both differ most in handling of dates, and the names of functions that handle dates.
Logical Operators uses C-language operators for logic. Ex: ‘foo’ || ‘bar’ means ‘foo’ OR ‘bar’, ‘foo’ && ‘bar’ means ‘foo’ and ‘bar’. This violates database standards and rules in a significant way Follows the standard, uses || for string concatenation Ex: ‘foo’ || ‘bar’ = ‘foobar’.