NoPublic, bureaucrat, administrator
6,632
edits
Changes
no edit summary
{{Warning| There are relation differences between {{IMSMANG}} 5.08.04 and 6.0 database meaning that FKeys constrains and cascading rules have been changed even for tables that existed in 5.08.04.}}
{{TOC right}}
== Introduction ==__NOEDITSECTION__
{{IMSMANG}} version 6.0 is building on a PostgreSQL database management system, whereas previous {{IMSMANG}} versions were built on a MySQL database. Both systems are relational database systems and using SQL, the Structured Query Language, as means for interacting with it. However, there are some differences between the MySQL and PosrgreSQL PostgreSQL syntax. It is not possible to list all differences here, but the main ones encountered in the {{IMSMANG}} world are highlighted below.
The biggest differences are:
* PostgreSQL is case sensitive
* Custom functions
For more details about the PostgreSQL syntax it is advised to consult the official PostgreSQL documentation at [http://www.postgresql.org/docs/9.1/static/index.html]. For more details about using SQL statements in {{IMSMANG}} import scripts contact your [[Information Management Team | GICHD IM advisor]].
{{Note | PostgreSQL complies to ANSI SQL standards, whereas MySQL does not.}}
== Changes Most common changes in database queries and views from MySQL to PostreSQL PostgreSQL ==__NOEDITSECTION__
When upgrading from {{IMSMANG}} 5.08.04 to 6.0, the database views need to be adapted, i.e. changed from MySQL syntax to PostgreSQL syntax. The country focal point is taking care of this. For reference, this section describes the main changes encountered. This can also be used as a tutorial for creating views in PostgreSQL, for {{IMSMANG}} administrators who were used to creating them in MySQL.
{{Note | If an column name in PostgreSQL is written with upper case but not enclosed into double quotes, it will be created in lower case in the database.}}
=== CAST AS CHAR CHARSET utf8 and _utf8 (mySQLMySQL) ===__NOEDITSECTION__
In some (older) MySQL versions, UTF-8 was not the default character set and strings thus needed to be explicitly converted to UTF-8. This is not the case in PostgreSQL and UTF-8 related syntax as used in MySQL does not work in PostgreSQL. Below are two examples encountered in {{IMSMANG}} views.
</pre>
=== IF and IFNULL (mySQLMySQL) / CASE (PostgreSQL) statements (conditional expressions) ===__NOEDITSECTION__
The statements IF() and IFNULL() do not exist in PostgreSQL. In PostgreSQL, the CASE statement is used for conditional expressions. The syntax of the CASE statement is as follows:
<pre>
{{Note | Although the PostgreSQL version can seem more "tedious" here, it is actually more correct and standard-compliant. In fact, it is important to specify all the groupings, otherwise the system (e.g. MySQL) will choose the remaining ones and apply some sorting to do so.}}
{{Important Warning| When upgrading views and making such changes/adaptations regarding the GROUP BY, it is important to compare the result set in both versions!}}
The MySQL way of using the GROUP BY can also be emulated by the '''DISTINCT ON''' in PostgreSQL. The '''MySQL''' query
