From Koha Wiki
The intention of this Wiki page is to collect information on how to run Koha on PostgtreSQL.
There is also a Bug open for this: [Bug 7365]
The Main Problem: MySQLisms
The main problem with running Koha on PostgreSQL are the many MySQLisms, that is SQL fragments that use MySQL extensions and that are non-standard SQL. The use of MySQLisms must be avoided at all cost. This page should help you to reach that goal.
|What||MySQL||PostgreSQL||Neutral SQL||C4:DBQ Function||DBH Function|
|The current date||CURDATE(), CURRENT_DATE()||now()::date||CAST(now() AS date)||n/a||n/a|
|Colum names that are keywords||`colname`||"colname"||??||n/a||n/a|
|Show columns in a table||SHOW COLUMNS FROM, DESCRIBE||SELECT column_name, data_type FROM information_schema.columns WHERE table_name ='tablename'||n/a||tableColumns("<tablename>")||column_info()|
 Actually we use the following statement to be consistent with the result set usage in Koha:
SELECT column_name as "Field", data_type as "Type" FROM information_schema.columns WHERE table_name ='tablename'
C4:DBQ - Creating SQL Query Fragments on the Fly
The proposed solution for idioms that do now have a portable form (patch pending) is to replace non-portable static SQL code by function calls that return database specific SQL fragements as strings to be concatenated with the static SQL code. This method is minimally invasive and does not need a paradigm change:
my $dbq = C4:DBQ->new(); $dbh->do("SELECT " . $dbq->today() . " AS today");
Here, the (fictious) function $dbq->today() would return 'CURDATE()' for MySQL or 'now()::date' for PostgreSQL. Please note that this is a bad example since 'CAST(now() AS date)' would work on either database.
- C4/DBQ.pm: Factory "class" to create a database specific object:
- C4/DBQ/MySQL.pm: MySQL implementation
- C4/DBQ/PostgreSQL.pm: PostgreSQL implementation
- t/DBQ.pl: Unit test
C4:DBQ->new() Returns either a MySQL or PostgreSQL object, depending on which database has been selected. If more database types are supported, it will return the respective object type.