Load data from PostgreSQL

Learn how to connect to a PostgreSQL database with Exasol and then load data.

To view details about data type mappings or to migrate data, see the PostgreSQL to Exasol migration script in our GitHub repository.

Prerequisites

  • The PostgreSQL server must be reachable from the Exasol system.

  • The user credentials in the connection must be valid.

Add JDBC driver

Create a configuration file called settings.cfg with the following settings:

Copy 
DRIVERNAME=PostgreSQL
PREFIX=jdbc:postgresql:
FETCHSIZE=100000
INSERTSIZE=-1
NOSECURITY=YES

The settings.cfg file must end with an empty line (line break followed by zero characters).

To learn how to upload the driver and the configuration file to an Exasol as-application deployment, see Add JDBC driver.

To learn how to upload the driver and the configuration file to Exasol SaaS, see Add JDBC driver (SaaS).

Create connection

To create a connection, run the following statement. Replace the connection string and credentials as needed.

Copy 
CREATE OR REPLACE CONNECTION JDBC_POSTGRESQL
 TO 'jdbc:postgresql://192.168.99.100:5432/my_db'
 USER 'username'
 IDENTIFIED BY 'Exasoltest';

To test the connection, run the following statement.

Copy 
 SELECT * FROM 
 (IMPORT FROM JDBC AT JDBC_POSTGRESQL
   STATEMENT 'select ''Connection works'' '
  );

Load data

Use IMPORT to load data from a table or SQL statement using the connection that you created.