Connect to PostgreSQL using the Database connector in Jitterbit Integration Studio

This page details how to connect to PostgreSQL using the Database connector.

You can connect to an PostgreSQL database using either a JDBC or ODBC driver. The JDBC driver is recommended for additional features, such as being able to provide a manual query, that are not available through an ODBC connection. The PostgreSQL drivers should already be available on the Jitterbit agent, as the agent currently uses PostgreSQL to store internal data.

Client encoding

If you receive a "client encoding mismatch" error when you test the connection to a PostgreSQL database, this means the encoding that the PostgreSQL server you are connecting to is different than the default encoding assumed by the PostgreSQL driver.

To resolve this issue, set the client encoding on Windows to WIN1251 and specify the encoding during configuration of a Database connection:

• Additional Connection String Parameters: Under Optional Settings, enter this additional parameter:

  ConnSettings=SET CLIENT_ENCODING to 'LATIN1'
  

Linux users

When running the Jitterbit agent on Linux, always use the PostgreSQL driver installed with the agent. The PostgreSQL driver installed with many distributions as part of unixODBC does not work well with Harmony and produces errors.

WHERE clause examples

These WHERE clause examples are provided for reference purposes when configuring a Database query activity:

WHERE first_name = 'Jamie' AND
 last_name = 'Rice';

WHERE amount <= 1 OR amount >= 8;

WHERE distributors.name LIKE 'W%'

WHERE did = $1;

WHERE manager_name = 'Mary'

WHERE er.employee_name = e.manager_name

For additional examples, refer to PostgreSQL Tutorial and the PostgreSQL Manual for your version.