PostgreSQL Query

This is a variant of the SQL Query node meant for use with PostgreSQL databases.

Basic Information

Node Settings

• SQL Query Connection (string, required) : A connection string or connection tag.

• Timeout (int, optional) : Sets the SQL command timeout.

• Output Table (string, optional) : The name given to the tables in the output.

• SQL Queries (List<string>, required) : The list of queries the node executes.

If using a connection tag in the SQL Query Connection setting make sure the tag exists in the database!

Tips and Advanced usage

Use this node to read data from a PostgreSQL database by using SQL queries. It can execute any query. You can set up multiple queries to extract multiple tables.

Examples

Example 1

This example extracts all the data from a table named ExperimentData. It uses a connection tag for the connection.

{
  "properties": {
    "Connection": "<storage>",
    "Timeout": 90,
    "ResultName": "ExportedData",
    "Queries": [
      "SELECT * FROM ExperimentData"
    ]
  }
}

Example 2

This example extracts the data from the ExperimentData table but the command is filtered for the current user and the selected project.

In order for this node to work, the project metadata must be already available in the process contex (i. e. it must be selected from a form or stored as metadata in some other way). The username metadata is built in metadata that will reflect the username of the currently logged user.

{
  "properties": {
    "Connection": "<storage>",
    "Timeout": 90,
    "ResultName": "ExportedData",
    "queries": [
      "SELECT * FROM ExperimentData WHERE "User" = @username AND "Project" = @project"
    ]
  }
}