PostgreSQL

Execute SQL operations against PostgreSQL databases with parameterized queries for safe data retrieval and manipulation.

Overview

Performs CRUD operations on a PostgreSQL database. Six operations are available: Select (query rows with WHERE / ORDER BY / LIMIT), Insert (add rows by auto-mapping the incoming item or by defining columns yourself), Update (modify rows matched by one or more columns), Upsert (insert a row or update it on conflict), Delete (truncate a table, delete matching rows, or drop the table) and Execute Query (raw SQL with numbered placeholders). Values you supply are sent to the database as query parameters rather than pasted into the statement text. SSL connections and array and JSON column types are handled for you.

Category: Data & Storage
Tool Name: postgres
Version: 1

Appearance: Icon: si-postgresql | Color: #336791

Node Type

Action — processes input items and produces output

Input / Output

Direction	Port(s)
Input	Input
Output	Output, Error

Credentials

This tool requires PostgreSQL credentials. See the Credentials Guide for setup instructions.

Operations

Operation	Value	Description
Delete	deleteRows	Delete rows, truncate, or drop a table
Execute Query	executeQuery	Execute an arbitrary SQL query with parameterized values
Insert	insert	Insert one or more rows into a table
Select	select	Select rows from a table
Update	update	Update existing rows matched by column(s)
Upsert	upsert	Insert a row or update it if it already exists (ON CONFLICT)

Parameters

Delete (deleteRows)

Parameter	Type	Required	Default	Description
Schema	string	Yes	public	The database schema that contains the table. Find schemas with: SELECT schema_name FROM information_schema.schemata
Table	string	Yes	—	The table to operate on. Find tables with: SELECT table_name FROM information_schema.tables WHERE table_schema = ‘public’
Command	options	No	truncate	The type of delete operation.
				Options: truncate (remove all rows but keep the table structure), delete (delete rows matching conditions, or all rows if no conditions), drop (remove the table entirely)
Restart Sequences	boolean	No	false	Whether to reset identity (auto-increment) columns to initial values. (shown when Command is truncate)
Select Rows (WHERE) (deleteWhere)	fixedCollection	No	{}	Conditions for which rows to delete. If not set, all rows are deleted. (shown when Command is delete)
— Column	string	No	—	Column name to filter on.
— Operator	options	No	equal	Comparison operator.
				Options: equal, !=, LIKE, >, <, >=, <=, IS NULL, IS NOT NULL
— Value	string	No	—	Value to compare against. (hidden when Operator is IS NULL, IS NOT NULL)
Combine Conditions (Delete)	options	No	AND	How to combine the WHERE conditions for delete. (shown when Command is delete)
				Options: AND, OR

Execute Query (executeQuery)

Parameter	Type	Required	Default	Description
Query	string	Yes	—	SQL query to execute. Use $1, $2, $3, etc. for parameterized values (set in Options > Query Parameters). NEVER concatenate user input directly into the query.

Insert (insert)

Parameter	Type	Required	Default	Description
Schema	string	Yes	public	The database schema that contains the table. Find schemas with: SELECT schema_name FROM information_schema.schemata
Table	string	Yes	—	The table to operate on. Find tables with: SELECT table_name FROM information_schema.tables WHERE table_schema = ‘public’
Data Mode	options	No	autoMapInputData	How to map input data to table columns.
				Options: autoMapInputData (use input item properties as column values — property names must match column names), defineBelow (manually specify column values as a JSON object)
Column Values	json	No	{}	JSON object of column-name to value pairs. Example: { “name”: “John”, “email”: “john@example.com” } (shown when Data Mode is defineBelow)

Select (select)

Parameter	Type	Required	Default	Description
Schema	string	Yes	public	The database schema that contains the table. Find schemas with: SELECT schema_name FROM information_schema.schemata
Table	string	Yes	—	The table to operate on. Find tables with: SELECT table_name FROM information_schema.tables WHERE table_schema = ‘public’
Return All	boolean	No	true	Whether to return all results or only up to a given limit.
Limit	number	No	50	Max number of rows to return. (shown when Return All is false)
Select Rows (WHERE) (where)	fixedCollection	No	{}	Filter conditions. If not set, all rows are returned.
— Column	string	No	—	Column name to filter on.
— Operator	options	No	equal	Comparison operator.
				Options: equal, !=, LIKE, >, <, >=, <=, IS NULL, IS NOT NULL
— Value	string	No	—	Value to compare against. Not used for IS NULL / IS NOT NULL. (hidden when Operator is IS NULL, IS NOT NULL)
Combine Conditions	options	No	AND	How to combine the WHERE conditions.
				Options: AND (all conditions must be true), OR (at least one condition must be true)
Sort	fixedCollection	No	{}	ORDER BY rules.
— Column	string	No	—	Column to sort by.
— Direction	options	No	ASC	Sort direction.
				Options: ASC, DESC

Update (update)

Parameter	Type	Required	Default	Description
Schema	string	Yes	public	The database schema that contains the table. Find schemas with: SELECT schema_name FROM information_schema.schemata
Table	string	Yes	—	The table to operate on. Find tables with: SELECT table_name FROM information_schema.tables WHERE table_schema = ‘public’
Data Mode	options	No	autoMapInputData	How to map input data to table columns.
				Options: autoMapInputData (use input item properties as column values — property names must match column names), defineBelow (manually specify column values as a JSON object)
Column Values	json	No	{}	JSON object of column-name to value pairs. Example: { “name”: “John”, “email”: “john@example.com” } (shown when Data Mode is defineBelow)
Matching Columns	string	Yes	id	Comma-separated column name(s) to match rows on. For update: identifies the row to update. For upsert: must have UNIQUE or PRIMARY KEY constraint. Example: “id” or “email,tenant_id”.

Upsert (upsert)

Parameter	Type	Required	Default	Description
Schema	string	Yes	public	The database schema that contains the table. Find schemas with: SELECT schema_name FROM information_schema.schemata
Table	string	Yes	—	The table to operate on. Find tables with: SELECT table_name FROM information_schema.tables WHERE table_schema = ‘public’
Data Mode	options	No	autoMapInputData	How to map input data to table columns.
				Options: autoMapInputData (use input item properties as column values — property names must match column names), defineBelow (manually specify column values as a JSON object)
Column Values	json	No	{}	JSON object of column-name to value pairs. Example: { “name”: “John”, “email”: “john@example.com” } (shown when Data Mode is defineBelow)
Matching Columns	string	Yes	id	Comma-separated column name(s) to match rows on. For update: identifies the row to update. For upsert: must have UNIQUE or PRIMARY KEY constraint. Example: “id” or “email,tenant_id”.

All Operations

Parameter	Type	Required	Default	Description
Options	collection	No	{}	Additional configuration for the operation.
— Cascade	boolean	No	false	Whether to CASCADE when truncating or dropping a table. (shown when Operation is deleteRows)
— Output Columns	string	No	*	Comma-separated list of columns to return, or * for all columns. Applies to select, insert, update, and upsert. (shown when Operation is select, insert, update, upsert)
— Query Parameters	string	No	—	Comma-separated values for $1, $2, $3, … placeholders in your query. (shown when Operation is executeQuery)
— Treat Query Parameters in Single Quotes as Text	boolean	No	false	When true, ‘$1’ in the query is treated as a text parameter rather than a string literal. (shown when Operation is executeQuery)
— Output Large-Format Numbers As	options	No	text	How to output BIGINT and NUMERIC columns. Text preserves precision for large numbers.
				Options: numbers, text (use for numbers longer than 16 digits to preserve precision)
— Skip on Conflict	boolean	No	false	Whether to skip the row if a unique constraint is violated (adds ON CONFLICT DO NOTHING). (shown when Operation is insert)
— Replace Empty Strings with NULL	boolean	No	false	Whether to replace empty string values with NULL before execution.
— Query Batching	options	No	single	How to batch queries sent to the database.
				Options: single (all items processed together), independently (one query per item, errors do not affect other items), transaction (all queries in a transaction; on failure, all changes roll back)
— Connection Timeout	number	No	30	Connection timeout in seconds.
Max Concurrency	number	No	10	Maximum number of items to process concurrently.

Output Data

Every returned row becomes its own output item, so one input item can produce many output items — a Select that matches 200 rows emits 200 items. The row’s columns are the item JSON: the input item’s own JSON is replaced rather than merged. Binary data on the input item is forwarded to every item produced from it.

When a statement returns no rows at all, the node emits a single item that keeps the input JSON and adds a marker: _queryResult: [] for Select, or success: true for every other operation.

Operation	Output item JSON
select	One item per matching row, holding the selected columns. { …input, "_queryResult": [] } when nothing matches.
insert	One item per inserted row, holding the columns named by the Output Columns option (all columns by default). With Skip on Conflict on, a skipped row returns nothing, so the item becomes { …input, "success": true }.
update	One item per updated row, holding the Output Columns.
upsert	One item per inserted or updated row, holding the Output Columns.
deleteRows	A single { "success": true } item, for all three commands. Row counts are not reported.
executeQuery	One item per returned row. Statements that return no rows (INSERT/UPDATE/DELETE without RETURNING, DDL) produce one { "success": true, "rowCount": n } item. A SELECT that matches nothing produces { …input, "success": true }.

Reference the values downstream by column name, e.g. {{ $json.email }}.

Usage Examples

• SELECT rows from a PostgreSQL table with WHERE filters
• INSERT a new row into a PostgreSQL table
• UPDATE existing rows matched by ID column
• UPSERT data using ON CONFLICT for idempotent writes
• Execute a raw parameterized SQL query
• TRUNCATE or DROP a table
• Delete rows matching specific conditions

Example Configuration

Read every row of a table:

{
  "type": "postgres",
  "parameters": {
    "operation": "select",
    "schema": "public",
    "table": "users",
    "returnAll": true
  }
}

Filter and sort, and cap how many rows come back:

{
  "type": "postgres",
  "parameters": {
    "operation": "select",
    "schema": "public",
    "table": "users",
    "returnAll": false,
    "limit": 50,
    "where": {
      "values": [
        { "column": "status", "condition": "equal", "value": "active" },
        { "column": "age", "condition": ">", "value": "18" }
      ]
    },
    "combineConditions": "AND",
    "sort": {
      "values": [
        { "column": "created_at", "direction": "DESC" }
      ]
    }
  }
}

Insert the incoming item as-is, matching its property names to column names:

{
  "type": "postgres",
  "parameters": {
    "operation": "insert",
    "schema": "public",
    "table": "users",
    "dataMode": "autoMapInputData"
  }
}

Insert a row you define yourself:

{
  "type": "postgres",
  "parameters": {
    "operation": "insert",
    "schema": "public",
    "table": "users",
    "dataMode": "defineBelow",
    "columnValues": {
      "name": "John Doe",
      "email": "john@example.com",
      "status": "active"
    }
  }
}

Update a row. The matching column has to be present in Column Values — that is where the WHERE value comes from:

{
  "type": "postgres",
  "parameters": {
    "operation": "update",
    "schema": "public",
    "table": "users",
    "dataMode": "defineBelow",
    "columnValues": {
      "id": 42,
      "status": "inactive"
    },
    "matchingColumns": "id"
  }
}

Insert or update in one step, keyed on a column with a unique constraint:

{
  "type": "postgres",
  "parameters": {
    "operation": "upsert",
    "schema": "public",
    "table": "users",
    "dataMode": "defineBelow",
    "columnValues": {
      "email": "user@example.com",
      "name": "Updated Name",
      "status": "active"
    },
    "matchingColumns": "email"
  }
}

Delete only the rows that match a condition:

{
  "type": "postgres",
  "parameters": {
    "operation": "deleteRows",
    "schema": "public",
    "table": "users",
    "deleteCommand": "delete",
    "deleteWhere": {
      "values": [
        { "column": "status", "condition": "equal", "value": "inactive" }
      ]
    },
    "deleteCombineConditions": "AND"
  }
}

Empty a table and reset its identity columns:

{
  "type": "postgres",
  "parameters": {
    "operation": "deleteRows",
    "schema": "public",
    "table": "logs",
    "deleteCommand": "truncate",
    "restartSequences": true
  }
}

Run your own SQL, passing the value for $1 through Query Parameters:

{
  "type": "postgres",
  "parameters": {
    "operation": "executeQuery",
    "query": "SELECT u.name, COUNT(o.id) AS order_count FROM users u LEFT JOIN orders o ON u.id = o.user_id WHERE u.created_at > $1 GROUP BY u.id, u.name",
    "options": {
      "queryReplacement": "2023-01-01"
    }
  }
}

Return a subset of columns, blank out empty strings, and allow a slower connection:

{
  "type": "postgres",
  "parameters": {
    "operation": "select",
    "schema": "public",
    "table": "financial_data",
    "returnAll": true,
    "options": {
      "outputColumns": "id,amount,description",
      "replaceEmptyStrings": true,
      "connectionTimeout": 60
    }
  }
}

Bulk-load events, skipping anything that violates a unique constraint:

{
  "type": "postgres",
  "parameters": {
    "operation": "insert",
    "schema": "analytics",
    "table": "events",
    "dataMode": "autoMapInputData",
    "maxConcurrency": 5,
    "options": {
      "skipOnConflict": true
    }
  }
}

Run a reporting query built from a CTE:

{
  "type": "postgres",
  "parameters": {
    "operation": "executeQuery",
    "query": "WITH monthly_sales AS (SELECT DATE_TRUNC('month', created_at) AS month, SUM(amount) AS total FROM orders WHERE created_at >= $1 GROUP BY month) SELECT * FROM monthly_sales ORDER BY month DESC",
    "options": {
      "queryReplacement": "2023-01-01",
      "connectionTimeout": 120
    }
  }
}

Error Handling

Mode	Behavior
stop	Halts workflow on first error
continue	Skips failed items, passes successful ones through
errorPort	Routes failed items to Error output port

Tips

Execute SQL operations (SELECT, INSERT, UPDATE, UPSERT, DELETE, raw SQL) against PostgreSQL databases with parameterized queries. Use for any direct database interaction with PostgreSQL.

Values and identifiers

• Values are bound, not pasted. Everything you enter as a value — the Value field of a WHERE condition, the contents of Column Values, the auto-mapped properties of an input item, and the $1, $2 … placeholders of Execute Query — is handed to the database as a query parameter. Quotes and semicolons inside your data cannot change the statement.
• Identifiers are written into the statement. Schema, Table, the Column fields inside Select Rows (WHERE) and Sort, Matching Columns and Output Columns name database objects, so their text becomes part of the SQL (double-quoted and escaped as identifiers). Keep those fields under your own control — pin them to fixed values rather than filling them from data that arrives with the item.
• Execute Query runs exactly what you write. Only the placeholders are bound; the rest of the string is sent as-is. Build the statement yourself and route every incoming value through Query Parameters instead of concatenating it into the text.
• Query Parameters is a comma-separated list, so it cannot carry a value that itself contains a comma. Reach for one of the structured operations when your data might.

Choosing an operation

• Delete is three different statements. truncate empties the table and keeps it, delete removes only the rows your conditions match — and removes every row when you set no conditions — and drop removes the table itself. Only delete reads Select Rows (WHERE); only truncate reads Restart Sequences.
• Update and Upsert both need Matching Columns. Update uses them to locate the row to change. Upsert uses them as the conflict target, so they must carry a UNIQUE or PRIMARY KEY constraint. Pass a comma-separated list for a composite key, e.g. email,tenant_id.
• Auto-map is name-for-name. In autoMapInputData mode every property on the input item is treated as a column, so trim the item with an Edit Fields node first if it carries anything the table does not have.
• Use Output Columns to keep items small. Insert, Update and Upsert return the affected rows in full by default; naming just the columns you need downstream keeps the output narrow.