NoPublic, bureaucrat, administrator
1,068
edits
Changes
no edit summary
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].
{{Note | PostgreSQL complies to ANSI SQL standards, whereas MySQL does not.}}
== Changes in database views from MySQL to PostreSQL ==__NOEDITSECTION__
=== The GROUP BY clause ===__NOEDITSECTION__
MySQL has much more "relaxed" rules about using the GROUP BY clause than PostgreSQL has. Therefore, adaptations might need to be done in views using grouping with GROUP BY.
The things to consider are the following:
* '''PostgreSQL''': When GROUP BY is present, it is not valid for the SELECT list expressions to refer to ungrouped columns except within aggregate functions or if the ungrouped column is functionally dependent on the grouped columns (cf. [http://www.postgresql.org/docs/9.1/static/sql-select.html#SQL-GROUPBY]). In other words, when the SELECT part of a query or create view statement contains three columns without aggregation and two with an aggregation function, all three columns without the aggregation '''must''' be specified in the GROUP BY clause. This is not the case in MySQL.
An example best illustrates that behavior. Consider this '''MySQL''' view from an {{IMSMANG}} deployment in a country:
<pre>
CREATE VIEW `annualreport_view_bac` AS
select `progress`.`hazreduc_guid` AS `ProgressGUID`,
cast(`progress`.`startdate` as date) AS `StartofReportingPeriod`,
cast(`progress`.`enddate` as date) AS `EndofReportingPeriod`,
`progress`.`areasize` AS `AreaCleared`,
sum(if((`annualreport_view_devicesdestroyed`.`Model` = _utf8'SAA'),`annualreport_view_devicesdestroyed`.`Quantity`,0)) AS `SAA`,
sum(if((`annualreport_view_devicesdestroyed`.`Model` = _utf8'AP'),`annualreport_view_devicesdestroyed`.`Quantity`,0)) AS `AP`,
sum(if((`annualreport_view_devicesdestroyed`.`Model` = _utf8'UXO'),`annualreport_view_devicesdestroyed`.`Quantity`,0)) AS `UXO`,
sum(if((`annualreport_view_devicesdestroyed`.`Model` = _utf8'AT'),`annualreport_view_devicesdestroyed`.`Quantity`,0)) AS `AT`,
sum(if((`annualreport_view_devicesdestroyed`.`Model` = _utf8'Fragments'),`annualreport_view_devicesdestroyed`.`Quantity`,0)) AS `Fragments`,
sum(if((`annualreport_view_devicesdestroyed`.`Model` = _utf8'AIED'),`annualreport_view_devicesdestroyed`.`Quantity`,0)) AS `AIED`,
`annualreport_view_clearancetype`.`Clearance Type` AS `ClearanceType`,
`my_view_organization`.`ShortName` AS `Agency`,
`my_view_gazetteer`.`Region` AS `Region`,
`my_view_gazetteer`.`Province` AS `Province`,
`my_view_gazetteer`.`District` AS `District`,
`my_view_gazetteer`.`Village` AS `Village`
from (((((`hazreduc` `Progress`
join `imsmaenum` on((`progress`.`hazreductypeenum_guid` = `imsmaenum`.`imsmaenum_guid`)))
left join `annualreport_view_devicesdestroyed` on((`annualreport_view_devicesdestroyed`.`ProgressGUID` = `progress`.`hazreduc_guid`)))
join `annualreport_view_clearancetype` on((`annualreport_view_clearancetype`.`Hazreduc_guid` = `progress`.`hazreduc_guid`)))
join `my_view_organization` on((`progress`.`org_guid` = `my_view_organization`.`org_guid`)))
join `my_view_gazetteer` on((`my_view_gazetteer`.`location_guid` = `progress`.`location_guid`)))
where (`imsmaenum`.`enumvalue` = _utf8'progress report')
group by `progress`.`hazreduc_guid`;
</pre>
Taking into account all the changes to be made as described on this page, and looking into the GROUP BY clause issue, the following code would be equivalent in '''PostgreSQL''':
<pre>
CREATE VIEW annualreport_view_bac AS
select progress.hazreduc_guid AS "ProgressGUID",
cast(progress.startdate as date) AS "StartofReportingPeriod",
cast(progress.enddate as date) AS "EndofReportingPeriod",
progress.areasize AS "AreaCleared",
sum(case when annualreport_view_devicesdestroyed.Model = 'SAA' then annualreport_view_devicesdestroyed.Quantity else 0 end) AS "SAA",
sum(case when annualreport_view_devicesdestroyed.Model = 'AP' then annualreport_view_devicesdestroyed.Quantity else 0 end) AS "AP",
sum(case when annualreport_view_devicesdestroyed.Model = 'UXO' then annualreport_view_devicesdestroyed.Quantity else 0 end) AS "UXO",
sum(case when annualreport_view_devicesdestroyed.Model = 'AT' then annualreport_view_devicesdestroyed.Quantity else 0 end) AS "AT",
sum(case when annualreport_view_devicesdestroyed.Model = 'Fragments' then annualreport_view_devicesdestroyed.Quantity else 0 end) AS "Fragments",
sum(case when annualreport_view_devicesdestroyed.Model = 'AIED' then annualreport_view_devicesdestroyed.Quantity else 0 end) AS "AIED",
annualreport_view_clearancetype."Clearance Type" AS "ClearanceType",
my_view_organization.ShortName AS "Agency",
my_view_gazetteer.Region AS "Region",
my_view_gazetteer.Province AS "Province",
my_view_gazetteer.District AS "District",
my_view_gazetteer.Village AS "Village"
from hazreduc progress
join imsmaenum on (progress.hazreductypeenum_guid = imsmaenum.imsmaenum_guid)
left join annualreport_view_devicesdestroyed on (annualreport_view_devicesdestroyed.ProgressGUID = progress.hazreduc_guid)
join annualreport_view_clearancetype on (annualreport_view_clearancetype.Hazreduc_guid = progress.hazreduc_guid)
join my_view_organization on (progress.org_guid = my_view_organization.org_guid)
join my_view_gazetteer on (my_view_gazetteer.location_guid = progress.location_guid)
where (imsmaenum.enumvalue = 'Progress Report')
group by progress.hazreduc_guid,
cast(progress.startdate as date),
cast(progress.enddate as date),
progress.areasize,
annualreport_view_clearancetype."Clearance Type",
my_view_organization.ShortName,
my_view_gazetteer.Region,
my_view_gazetteer.Province,
my_view_gazetteer.District,
my_view_gazetteer.Village;
</pre>
== References ==__NOEDITSECTION__