Actions3
Overview
This node provides integration with a PostgreSQL database, allowing users to execute SQL queries, insert new rows, or update existing rows in the database. It is useful for automating data workflows that involve reading from or writing to PostgreSQL databases directly within n8n.
Common scenarios include:
- Running custom SQL queries to fetch or manipulate data.
- Inserting multiple rows into a specified table.
- Updating rows based on key columns.
- Executing multiple queries either independently, as a batch, or inside a transaction.
Practical examples:
- Fetching product details where quantity and price meet certain criteria.
- Bulk inserting new customer records into a sales database.
- Updating order statuses based on external triggers.
Properties
| Name | Meaning |
|---|---|
| Additional Fields | Collection of optional settings: • Mode: How queries are sent to the database: – Independently (each query separately) – Multiple Queries (default, all at once) – Transaction (all queries in one transaction) • Output Large-Format Numbers As: Format for NUMERIC and BIGINT columns: – Numbers – Text (recommended for numbers longer than 16 digits) • Query Parameters: Comma-separated list of input properties used as parameters in SQL queries (only for Execute Query operation). |
Output
The node outputs JSON data representing the results of the executed database operations. The structure depends on the operation:
- For Execute Query, it returns the result set(s) of the SQL query.
- For Insert and Update, it returns the affected rows or fields as specified by the "Return Fields" property.
If large-format numbers are present (NUMERIC or BIGINT), they can be output either as JavaScript numbers or as text strings to avoid precision loss.
Binary data is not explicitly handled or output by this node.
Dependencies
- Requires a PostgreSQL database connection configured via an API authentication credential containing host, port, database name, user, password, and optional SSL certificates.
- Uses the
pg-promiselibrary internally to manage database connections and queries. - SSL configuration supports disabling verification or providing certificates for secure connections.
Troubleshooting
- Connection errors: Often caused by incorrect credentials, wrong host/port, or SSL misconfiguration. Verify all connection parameters and SSL settings.
- Query syntax errors: Ensure SQL queries are valid and parameters match placeholders (
$1,$2, etc.). - Large number precision issues: If numeric values exceed 16 digits, set "Output Large-Format Numbers As" to "Text" to prevent incorrect parsing.
- Unsupported operation error: Occurs if an invalid operation is selected; only "executeQuery", "insert", and "update" are supported.
- Transaction failures: When using transaction mode, any failing query will rollback all changes. Use "Independently" mode to isolate queries if needed.