PostgreSQL Component

Query and manage data in a PostgreSQL database

Component key: postgres

Description

PostgreSQL is a popular relational database system. This component allows you to query a PostgreSQL database.

Query formatting

Instead of generating dynamic queries and sanitizing SQL yourself, you can use generic queries with query formatting. Formatted queries can either use numbered parameter placeholders like $1, $2, etc., or can use named parameter placeholders like ${variableKey}.

Index variables

Index variables are numbered parameter placeholders like $1, $2, etc. To use index variables, write a query that uses indexed placeholders (e.g. SELECT * FROM users WHERE id = $1 AND name = $2). Then, generate an array like [5, "John Doe"] and reference it from the Parameters Object or Array input.

Note: If you use indexed variables, you cannot use named variables in the same query.

Named variables

Named variables are written in the form ${variableKey}, $<variableKey> or $(variableKey). For example, you can enter a query that reads INSERT INTO users (name, email) VALUES (${name}, ${email}). Then, you can enter values with keys of name and email, and whatever values you like as Named Parameters Inputs. Values provided as variables are sanitized automatically.

If you are dynamically generating SQL queries and do not know what parameters you will need ahead of time, create a key/value object in another step and reference them through the Parameters Object or Array input. In the example above, you could generate an object of the form {name: "John doe", email: "john.doe@example.com"}.

Connections

PostgreSQL Connection

Create a new PostgreSQL connection and enter the host, port, and database for your PostgreSQL server. The username and password are optional inputs that can be put directly into a PostgreSQL connection.

On-prem enabled: this connection can be configured to connect to an on-prem resource on a private network. Learn more.

Input	Notes	Example
Database	The database in PostgreSQL	admin
Host	Provide the string value for the host of the server.	192.168.0.1
Password
Port	The port of the PostgreSQL server.	5432
Require SSL	Require SSL for the connection to the PostgreSQL server	false
Connection Timeout	The amount of time (in milliseconds) to wait for a connection to be established before timing out. Default is 5000ms.	5000
Username

Triggers

New and Updated Records to Table

Checks for new and updated records to a table | key: pollTable

Input	Notes	Example
Cast timestamps to strings	Select this option if your cursor field is a timestamp. PostgreSQL tracks microseconds, but JavaScript dates are measured in milliseconds. When fetching TIME, TIMETZ, TIMESTAMP, TIMESTAMPTZ fields, some precision can be lost. By casting timestamp values to strings, you can retain precision.	true
Cursor Field	A field that is used to track new results. If your table has an auto incrementing integer ID, you can use the ID. If it has a 'created at' or 'updated at' timestamp, you can use those. Each time this trigger runs, it checks for records with values that are greater than the largest value from last time it was run.	updated_at
Connection
Table Name		people

---

Actions

Query

Performs a query on a PostgreSQL database. | key: query

Input	Notes	Example
Debug Query	Enabling this flag will log out the query before being sent.	false
Named Parameters	Optional named parameters to insert into a query.
Parameters Object or Array	Optional parameters to insert into a query. This should be a key-value object if you are using named inputs (i.e. ${name}), or an array if using index variables (i.e. $2) in your query. Values from this object will be merged with Named Parameters inputs if you are using named variables.
Connection
Query Field	The query to be executed

---