PostgreSQL Component
![](/docs/img/components/icons/60/cG9zdGdyZXM=.png)
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 |