Actions3
Overview
This node allows updating rows in a PostgreSQL database table. It is designed to modify existing data based on specified keys and columns, making it useful for workflows that need to synchronize or correct data in a Postgres database.
Common scenarios include:
- Updating user information records when details change.
- Modifying product inventory or pricing data in an e-commerce system.
- Adjusting status flags or timestamps in database tables as part of automation processes.
For example, you could update the "description" and "price" columns of products identified by their "id" in a "products" table within the "public" schema.
Properties
| Name | Meaning |
|---|---|
| Schema | Name of the schema the table belongs to (default: "public"). |
| Table | Name of the table in which to update data. This is required. |
| Update Key | Comma-separated list of properties used to identify which rows to update. Typically this is "id". |
| Columns | Comma-separated list of properties to be updated as columns. You can specify type casting using colons, e.g., id:int. |
| Return Fields | Comma-separated list of fields that the operation will return after the update. Default is "*", meaning all fields. |
| Additional Fields | Collection of optional settings: • Mode: How queries are sent to the database. Options: - Independently: Execute each query separately. - Multiple Queries (default): Send multiple queries at once. - Transaction: Execute all queries in a single transaction. • Output Large-Format Numbers As: How to output NUMERIC and BIGINT columns. Options: "Numbers" or "Text" (use text for numbers longer than 16 digits). |
Output
The node outputs JSON data representing the rows affected by the update operation. The structure corresponds to the fields specified in the "Return Fields" property. If "*" is used, all columns of the updated rows are returned.
No binary data output is produced by this node.
Dependencies
- Requires a PostgreSQL database connection with appropriate credentials.
- Needs an API key credential or equivalent authentication configured in n8n for connecting to the Postgres instance.
- Uses the
pg-promiselibrary internally to interact with the database. - Supports SSL configuration options for secure connections.
Troubleshooting
- Connection errors: Ensure the database host, port, user, password, and SSL settings are correctly configured. If using SSL, verify certificates are valid or allow unauthorized certs if testing.
- Update key issues: If no rows are updated, check that the "Update Key" matches existing rows in the table.
- Invalid column names or types: Make sure the "Columns" property lists valid column names and uses correct type casting syntax if needed.
- Large number precision: For very large numeric values, set "Output Large-Format Numbers As" to "Text" to avoid precision loss.
- Transaction failures: When using "Transaction" mode, any error in one query rolls back all updates. Use "Independently" mode to continue processing other rows despite some failures.
- Unsupported operations: Only "executeQuery", "insert", and "update" operations are supported. Using others will throw an error.