Load data from PostgreSQL

This article describes 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

To add the 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

Follow the procedure described in Add JDBC Driver to upload the settings.cfg configuration and JDBC driver files to Exasol.

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.