PostgreSQL is an open source, object-relational database system that uses and extends the SQL language combined with other features that safely store and scale data workloads.

The K3 PostgreSQL source adaptor supports pulling data from a PostgreSQL Server database. The following instructions will walk through how to set up the PostgreSQL Server adaptor. 

1. Select PostgreSQL as the source adaptor

2. Click Database Credentials

Enter the connection information and click Test Credentials. Once a checkmark appears next to Test Credentials, click OK. Clicking OK will close the credentials pop up window, bringing the user back to the configuration page.

3. Click Build Query

From the configuration page, click Build Query. A pop up window will appear with a premade SQL query started for the user. At this point the user may follow one of the options below:

Option 1: The user can  keep the premade query and select a table from the drop down menu. This will pull all data from the chosen table without any filtering.

Option 2:  A custom query can be written in place of the premade query to pull from any available table with whatever filtering is desired.

Once the query is written, click Execute Query. A sample of the data received will be displayed in the right hand window. 

4. Select the delta poll parameter

On the right-hand side of the query pop-up box, K3 will highlight columns it recommends using for delta polling. K3 will only recommend columns that are DateTime columns or incrementing integer columns. K3 will not allow users to select a column that is not highlighted. If the user wants to use a non-highlighted column as the delta poll parameter, the user must manually enter the delta poll parameter.

To use one of the recommended columns for delta polling, add a WHERE clause with the column's name followed by a greater than sign, then click the column name from the right-hand plane.  

Example: WHERE snapshot_timestamp > ${rs.col('snapshot_timestamp').max()|$date.toDate('iso_tz','1970-01-01T00:00:00Z')}. 

In this example, the delta poll parameter will be snapshot_timestamp. Each new poll of the database will only ingest data with a snapshot timestamp that occurred after the latest snapshot timestamp K3 previously ingested.

Once the delta poll parameter has been chosen, click OK.

5. Adjust the poll schedule

Finally, the poll schedule needs to be determined. K3 currently supports periodic schedule and Cron scheduling, which is the default.  

6. Name the Route and click Create

Once the database connection is established, the SQL query is approved, and the poll schedule is set, name the route by click Route Name located at the top left of the configuration page, then click Create to create the route.