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	Default	Notes	Example
Database string / Required database		The database in PostgreSQL	admin
Host string / Required host		Provide the string value for the host of the server.	192.168.0.1
Password password password
Port string / Required port	5432	The port of the PostgreSQL server.
Require SSL boolean / Required requireSSL	false	Require SSL for the connection to the PostgreSQL server
Connection Timeout string timeout	5000	The amount of time (in milliseconds) to wait for a connection to be established before timing out. Default is 5000ms.
Username string username

Triggers

New and Updated Records to Table

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

Input	Default	Notes	Example
Cast timestamps to strings boolean castTimestampsToString	true	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.
Cursor Field string / Required cursorField	updated_at	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.
Connection connection / Required postgresConnection
Table Name string / Required tableName			people

---

Actions

Query

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

Input	Default	Notes	Example
Debug Query boolean debug	false	Enabling this flag will log out the query before being sent.
Named Parameters data Key Value List params		Optional named parameters to insert into a query.
Parameters Object or Array code paramsObject		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 connection / Required postgresConnection
Query Field code / Required queryField		The query to be executed

---