Exporting Data to Postgres SQL

The process to export data to a Postgres DB is described in the following sections:

Define the Postgres Target Data Connection

The following are the steps to configure a Postgres data connection:

1. Navigate to the required domain and click the Target Data Connections icon.
2. Click Add Connection.
3. In the Add Data Connection page, provide the following connection details.
4. Click Save.

Create a Pipeline to Add Postgres Node

Following are the steps to add a new pipeline to the domain:

1. Click the Domains menu and click the required domain from the list. You can also search for the required domain.
2. Click the Pipelines icon.
3. In the Pipelines page, click the New Pipeline button.
4. Enter the following details.
5. Click Save. The new pipeline will be added to the list of pipelines.

Design a Pipeline

Following are the steps to design a pipeline:

1. In the Pipelines page, click the new pipeline created. The Overview page is displayed with the pipeline version details and a blank design space.
2. Click Open Editor or click on the blank design space to open the editor page.
3. From the left panel, drag and drop the required Source tables from the Datalake Tables tab, and Postgres Target node from the Transformations tab, to the pipeline editor and connect them to design a pipeline.

Postgres Target Node Configuration

Following are the steps to apply Postgres Table Target node in pipeline:

1. Double-click the Postgres Table node. The Properties page is displayed.
2. Click Edit Properties.
3. Click Save.

$inline[badge,NOTE,primary] Natural columns in Postgres merge must uniquely define rows in the target table. If you are using an existing table, ensure that unique indexes are created using natural columns. For non-existing tables, Infoworks creates unique indexes using natural columns during the first build. If you are changing an already built postgres target from Overwrite mode to Merge mode, ensure that unique indexes are already created using natural keys.

Export the Data

Following are the steps to build a pipeline:

1. Navigate to the required pipeline and click the Build icon.
2. Select the Version and click Build Pipeline to build the pipeline version.
3. You can click the Build Target Metadata button to enable import of multiple, sequential pipelines with export configuration.
4. Click any of the existing builds under the Recent Builds section to view the detailed information of the job. You can view the summary. You can also download logs of the job.

Supported Datatype Mappings

Below is the list of supported source datatypes and their mapping to Postgres datatypes