Postgres

These notes on running CORE on PostgreSQL are experimental

Databases & Schemas

By default CORE groups tables logically in several different named databases. In postgres these will be represented by schemas instead. Configuration options that say "database" effectively mean "schema" with postgres. The actual database name should be the same as the name of the user connecting to postgres.

Setup

Create a user, database, and schemas

CREATE ROLE youruser WITH LOGIN, PASSWORD='yourpassword';
CREATE DATABASE youruser WITH OWNER=youruser;
\connect youruser
CREATE SCHEMA core_op;
CREATE SCHEMA core_trans;
CREATE SCHEMA trans_archive;

Case sensitivity

Postgres strongly prefers case insensitive identifiers for table and column names. By default these are lowercase. When a name is lowercase, such as MyTable, you can refer to it in a query as mytable or MyTable or mytAble and all will work. However, case sensitive names can be created by escaping, i.e. "MyTable". Case sensitive identifiers must be referred to using correct case and the quotation escape characters. To avoid littering identifierEscape calls everywhere, tables and views are created as lowercase on Postgres.

Current status

Office can be installed on Postgres 9.4 and successfully creates all the underlying data structures. Only pgsql is supported and not pdo_pgsql. No rigorous testing has been done. UPDATE queries that include JOINs are almost certainly broken.