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.}}
{{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>