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

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

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

To know how to upload the JDBC driver and the configuration file to BucketFS, see Add JDBC Driver.

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.