Export data from PostgreSQL

Begin retrieving data from PostgreSQL by creating an export that will send your query as an HTTP request. You can start with a standalone export or add one in Flow Builder, as described below.

Create an export

1. Select Flow Builder from the Tools menu.
2. Choose PostgreSQL for the Source application.
3. Select a PostgreSQL connection from the Connection list.
4. Click Next.

The new export’s Create source pane opens for you to edit the settings:

Name (required): Name the export so that you can easily reference it from other parts of the application.

Description (optional): Enter a description for your export so that other users can quickly understand its purpose without having to examine all the fields and settings. Be sure to highlight any important nuances that others might need to know before using your export in their flows. Update the description whenever you make changes to the export in the future.

Query (required): Enter the query that retrieves records to be exported.

SQL Query editor

Click the button beside the Query field to open the SQL editor and refine your statement, including any handlebars variables or statements.

The editor allows you to preview the resolved SQL query, including any syntax errors. The Preview pane also displays Sample Data results from PostgreSQL for all export types below, except Delta.

Export types

Select one of the following export types:

All

Exports all records returned in the query each time the export runs.

SELECT "Name", "Email", "Phone", "Comments", "CustID", "Date" FROM "CUSTOMERS";

Delta

Exports all records with a date/time value for the queried field that is greater than the last time the data flow ran. The delta export query should have a date field that compares with the lastExportDateTime of the flow run.

SELECT "Name", "Email", "Phone" FROM public."CUSTOMERS" where "Date">={{lastExportDateTime}}

Once

This options exports any records that have not already been exported and also automatically updates records to mark them as exported. For example, you may want to export only those transaction records not already exported:

SELECT "Name", "Email", "Phone", "Comments" FROM "CUSTOMERS" where once=false

Configure once query

Specify the query to update each record as exported in the database. integrator.io will make a second request to the database to set this field to true for all records that were exported so that those same records are not exported again.

For example, in the following query, CUSTOMERS is the table name, once is the Boolean field to identify whether a record is exported or not, and Name is the unique identifier of the record.

UPDATE "CUSTOMERS" SET once=true WHERE "Name"={{data.Name}}

Test

Exports only one record by default to avoid syncing lots of data. This option is primarily used for testing purposes.

SELECT "Name", "Email", "Phone", "Comments", "CustID", "Date" FROM "CUSTOMERS";