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.
||Uses Non standard # to begin a comment Line
||PostgreSQL use ‘–‘ (double dash), as this is the ANSI standard, and both databases understand it.
||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.
||uses ` (accent mark or backtick) to quote system identifiers
||This is non standard. so not used/allowed
||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.
||Both differ most in handling of dates, and the names of functions that handle dates.
||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’.