Difference between revisions of "Difference MySQL PostGreSQL"

From IMSMA Wiki
Jump to: navigation, search
Line 58: Line 58:
  
 
=== CAST AS CHAR utf8 (mySQL) ===__NOEDITSECTION__
 
=== CAST AS CHAR utf8 (mySQL) ===__NOEDITSECTION__
 +
'''MySQL'''
 +
<pre>
 +
AND (`customdefinedfield`.`label` = _utf8'CDF-Area Reduction MDU')
 +
</pre>
 +
 +
'''PostgreSQL'''
 +
<pre>
 +
AND (customdefinedfield.label = 'CDF-Area Reduction MDU')
 +
</pre>
 +
 +
'''MySQL'''
 +
<pre>
 +
cast(`geopoint`.`latitude` as char charset utf8) AS `lat`
 +
</pre>
 +
 +
'''PostgreSQL'''
 +
<pre>
 +
cast(geopoint.latitude as char) AS lat
 +
</pre>
  
 
=== IF and IFNULL (mySQL) / CASE (PostgreSQL) statements ===__NOEDITSECTION__
 
=== IF and IFNULL (mySQL) / CASE (PostgreSQL) statements ===__NOEDITSECTION__

Revision as of 08:14, 19 June 2014

Ambox warning blue construction.png This page is under construction

Introduction

The biggest differences are:

  • PostGreSQL is case sensitive
  • Command/function
  • Dates are stored without format in PostGreSQL
  • Round off
  • Customised functions

Changes in database views from mySQL to PostreSQL

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.

Case sensitivity and column names

PostgreSQL, as opposed to MySQL, is case-sensitive. This means that strings need to be provided in exactly the way they are stored in the database. For example, in the table IMSMAENUM, one out of many values in the column ENUMVALUE is Progress Report, with capital P and capital R. In mySQL it was possible to write:

SELECT *
FROM imsmaenum
WHERE enumvalue = 'progress report'

and still get one row as result. In PostgreSQL, the same query will return no result.

Therefore, a create view statement like this:
mySQL

CREATE OR REPLACE VIEW my_view AS
SELECT ...
FROM imsmaenum, ...
WHERE imsmaenum.enumvalue = 'progress report';

needs to be changed to:
PostgreSQL

CREATE OR REPLACE VIEW my_view AS
SELECT ...
FROM imsmaenum, ...
WHERE imsmaenum.enumvalue = 'Progress Report';

Similarly, in mySQL it is possible to define column names with a mix of upper and lower case as well as blanks and other special characters without any trouble. In PostgreSQL, this is also possible, but in this case the column name must be enclosed in double quotes. For example, the following create view statement:
mySQL

CREATE VIEW my_view AS
SELECT hazreducdeviceinfo.hazreduc_guid AS hazreduc_guid,
ordnance.model AS Device Type
FROM hazreducdeviceinfo;

needs to be changed to:
PostgreSQL

CREATE VIEW my_view AS
SELECT hazreducdeviceinfo.hazreduc_guid AS hazreduc_guid,
ordnance.model AS "Device Type"
FROM hazreducdeviceinfo;

CAST AS CHAR utf8 (mySQL)

MySQL

AND (`customdefinedfield`.`label` = _utf8'CDF-Area Reduction MDU')

PostgreSQL

AND (customdefinedfield.label = 'CDF-Area Reduction MDU')

MySQL

cast(`geopoint`.`latitude` as char charset utf8) AS `lat`

PostgreSQL

cast(geopoint.latitude as char) AS lat

IF and IFNULL (mySQL) / CASE (PostgreSQL) statements

Date manipulations

The GROUP BY clause