PostgreSQL Adapter

The PostgreSQL adapter allows mapping existing tables on a remote PostgreSQL database into the schema of Polypheny-DB. One adapter can map multiple tables. This allows push-down of larger portions of a query. While the schema must be static and cannot be changed with Polypheny-DB, the data on the remote tables can be updated independently of Polypheny-DB. Polypheny-DB connects to the remote database system as a client and providing full support for transactions.

Adapter Settings

The PostgreSQL adapter has the following settings:

SettingDescription
hostThe name or IP address of the remote PostgreSQL instance.
portThe JDBC port number on the remote PostgreSQL instance.
databaseThe name of the database containing the tables to be mapped.
usernameThe username to be used for authenticating at the remote PostgreSQL instance.
passwordThe password to be used for authenticating at the remote PostgreSQL instance.
maxConnectionsThe maximum number of concurrent JDBC connections.
transactionIsolationWhich level of transaction isolation should be used. Available options: SERIALIZABLE, READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ
tablesA list of tables which should be imported. The name must be in the format schemaName.tableName. Multiple names need to be separated by a comma.

Deployment

The adapter can either be deployed using the Polypheny-UI (Adapters -> Sources) or using the following SQL statement:

ALTER ADAPTERS ADD uniqueName 
   USING 'org.polypheny.db.adapter.jdbc.sources.PostgresqlSource' 
   WITH '{database:"postgres",host:"localhost",maxConnections:"25",password:"polypheny",username:"postgres",port:"5432",transactionIsolation:"SERIALIZABLE",tables:"public.foo,public.bar"}'

Please make sure to adjust the settings according to your needs.

After the deployment, the specified tables are mapped into the public schema of Polypheny-DB. The tables and columns can be renamed. Furthermore, columns can be reordered and dropped (this is handled virtually; schema modifications are not forwarded to the remote database system). Dropped columns can be added again using the Polypheny-UI or the following SQL statement:

ALTER TABLE tableName ADD COLUMN physicalName AS name

physicalName thereby refers to the column name on the remote database system.