Postgres 节点#

使用 Postgres 节点可以自动化 Postgres 中的工作，并将 Postgres 与其他应用程序集成。n8n 内置支持广泛的 Postgres 功能，包括执行查询、向数据库插入和更新行。

在此页面上，您将找到 Postgres 节点支持的操作列表以及更多资源的链接。

凭证

请参考 Postgres 凭证获取身份验证设置指导。

此节点可用作 AI 工具

此节点可用于增强 AI 代理的能力。以这种方式使用时，许多参数可以自动设置，或通过 AI 指导的信息进行设置 - 在 AI 工具参数文档中了解更多信息。

操作#

删除：删除整个表或表中的行
执行查询：执行 SQL 查询
插入：在表中插入行
插入或更新：在表中插入或更新行
查询：从表中查询行
更新：更新表中的行

删除#

使用此操作可以删除整个表或表中的行。

输入这些参数：

Credential to connect with: Create or select an existing Postgres credential.
Operation: Select Delete.
Schema: Choose the schema that contains the table you want to work on. Select From list to choose the schema from the dropdown list or By Name to enter the schema name.
Table: Choose the table that you want to work on. Select From list to choose the table from the dropdown list or By Name to enter the table name.
Command: The deletion action to take:
- Truncate: Removes the table's data but preserves the table's structure.
  - Restart Sequences: Whether to reset auto increment columns to their initial values as part of the Truncate process.
- Delete: Delete the rows that match the "Select Rows" condition. If you don't select anything, Postgres deletes all rows.
  - Select Rows: Define a Column, Operator, and Value to match rows on.
  - Combine Conditions: How to combine the conditions in "Select Rows". AND requires all conditions to be true, while OR requires at least one condition to be true.
- Drop: Deletes the table's data and structure permanently.

Delete options#

Cascade: Whether to also drop all objects that depend on the table, like views and sequences. Available if using Truncate or Drop commands.
Connection Timeout: The number of seconds to try to connect to the database.
Delay Closing Idle Connection: The number of seconds to wait before considering idle connections eligible for closing.
Query Batching: The way to send queries to the database:
- Single Query: A single query for all incoming items.
- Independently: Execute one query per incoming item of the execution.
- Transaction: Execute all queries in a transaction. If a failure occurs, Postgres rolls back all changes.
Output Large-Format Numbers As: The format to output NUMERIC and BIGINT columns as:
- Numbers: Use this for standard numbers.
- Text: Use this if you expect numbers longer than 16 digits. Without this, numbers may be incorrect.

执行查询#

使用此操作来执行 SQL 查询。

输入这些参数：

Credential to connect with: Create or select an existing Postgres credential.
Operation: Select Execute Query.
Query: The SQL query to execute. You can use n8n expressions and tokens like $1, $2, and $3 to build prepared statements to use with query parameters.

Execute Query options#

Connection Timeout: The number of seconds to try to connect to the database.
Delay Closing Idle Connection: The number of seconds to wait before considering idle connections eligible for closing.
Query Batching: The way to send queries to the database:
- Single Query: A single query for all incoming items.
- Independently: Execute one query per incoming item of the execution.
- Transaction: Execute all queries in a transaction. If a failure occurs, Postgres rolls back all changes.
Query Parameters: A comma-separated list of values that you want to use as query parameters.
Output Large-Format Numbers As: The format to output NUMERIC and BIGINT columns as:
- Numbers: Use this for standard numbers.
- Text: Use this if you expect numbers longer than 16 digits. Without this, numbers may be incorrect.
Replace Empty Strings with NULL: Whether to replace empty strings with NULL in input. This may be useful when working with data exported from spreadsheet software.

插入#

使用此操作在表中插入行。

输入这些参数：

Credential to connect with: Create or select an existing Postgres credential.
Operation: Select Insert.
Schema: Choose the schema that contains the table you want to work on. Select From list to choose the schema from the dropdown list or By Name to enter the schema name.
Table: Choose the table that you want to work on. Select From list to choose the table from the dropdown list or By Name to enter the table name.
Mapping Column Mode: How to map column names to incoming data:
- Map Each Column Manually: Select the values to use for each column.
- Map Automatically: Automatically map incoming data to matching column names in Postgres. The incoming data field names must match the column names in Postgres for this to work. If necessary, consider using the edit fields (set) node before this node to adjust the format as needed.

Insert options#

Connection Timeout: The number of seconds to try to connect to the database.
Delay Closing Idle Connection: The number of seconds to wait before considering idle connections eligible for closing.
Query Batching: The way to send queries to the database:
- Single Query: A single query for all incoming items.
- Independently: Execute one query per incoming item of the execution.
- Transaction: Execute all queries in a transaction. If a failure occurs, Postgres rolls back all changes.
Output Columns: Choose which columns to output. You can select from a list of available columns or specify IDs using expressions.
Output Large-Format Numbers As: The format to output NUMERIC and BIGINT columns as:
- Numbers: Use this for standard numbers.
- Text: Use this if you expect numbers longer than 16 digits. Without this, numbers may be incorrect.
Skip on Conflict: Whether to skip the row if the insert violates a unique or exclusion constraint instead of throwing an error.
Replace Empty Strings with NULL: Whether to replace empty strings with NULL in input. This may be useful when working with data exported from spreadsheet software.

插入或更新#

使用此操作在表中插入或更新行。

输入这些参数：

Credential to connect with: Create or select an existing Postgres credential.
Operation: Select Insert or Update.
Schema: Choose the schema that contains the table you want to work on. Select From list to choose the schema from the dropdown list or By Name to enter the schema name.
Table: Choose the table that you want to work on. Select From list to choose the table from the dropdown list or By Name to enter the table name.
Mapping Column Mode: How to map column names to incoming data:
- Map Each Column Manually: Select the values to use for each column.
- Map Automatically: Automatically map incoming data to matching column names in Postgres. The incoming data field names must match the column names in Postgres for this to work. If necessary, consider using the edit fields (set) node before this node to adjust the format as needed.

Insert or Update options#

Connection Timeout: The number of seconds to try to connect to the database.
Delay Closing Idle Connection: The number of seconds to wait before considering idle connections eligible for closing.
Query Batching: The way to send queries to the database:
- Single Query: A single query for all incoming items.
- Independently: Execute one query per incoming item of the execution.
- Transaction: Execute all queries in a transaction. If a failure occurs, Postgres rolls back all changes.
Output Columns: Choose which columns to output. You can select from a list of available columns or specify IDs using expressions.
Output Large-Format Numbers As: The format to output NUMERIC and BIGINT columns as:
- Numbers: Use this for standard numbers.
- Text: Use this if you expect numbers longer than 16 digits. Without this, numbers may be incorrect.
Replace Empty Strings with NULL: Whether to replace empty strings with NULL in input. This may be useful when working with data exported from spreadsheet software.

查询#

使用此操作查询表中的行。

输入这些参数：

Credential to connect with: Create or select an existing Postgres credential.
Operation: Select Select.
Schema: Choose the schema that contains the table you want to work on. Select From list to choose the schema from the dropdown list or By Name to enter the schema name.
Table: Choose the table that you want to work on. Select From list to choose the table from the dropdown list or By Name to enter the table name.
Return All: Whether to return all results or only up to a given limit.
Limit: The maximum number of items to return when Return All is disabled.
Select Rows: Set the conditions to select rows. Define a Column, Operator, and Value to match rows on. If you don't select anything, Postgres selects all rows.
Combine Conditions: How to combine the conditions in Select Rows. AND requires all conditions to be true, while OR requires at least one condition to be true.
Sort: Choose how to sort the selected rows. Choose a Column from a list or by ID and a sort Direction.

Select options#

Connection Timeout: The number of seconds to try to connect to the database.
Delay Closing Idle Connection: The number of seconds to wait before considering idle connections eligible for closing.
Query Batching: The way to send queries to the database:
- Single Query: A single query for all incoming items.
- Independently: Execute one query per incoming item of the execution.
- Transaction: Execute all queries in a transaction. If a failure occurs, Postgres rolls back all changes.
Output Columns: Choose which columns to output. You can select from a list of available columns or specify IDs using expressions.
Output Large-Format Numbers As: The format to output NUMERIC and BIGINT columns as:
- Numbers: Use this for standard numbers.
- Text: Use this if you expect numbers longer than 16 digits. Without this, numbers may be incorrect.

更新#

使用此操作更新表中的行。

输入这些参数：

Credential to connect with: Create or select an existing Postgres credential.
Operation: Select Update.
Schema: Choose the schema that contains the table you want to work on. Select From list to choose the schema from the dropdown list or By Name to enter the schema name.
Table: Choose the table that you want to work on. Select From list to choose the table from the dropdown list or By Name to enter the table name.
Mapping Column Mode: How to map column names to incoming data:
- Map Each Column Manually: Select the values to use for each column.
- Map Automatically: Automatically map incoming data to matching column names in Postgres. The incoming data field names must match the column names in Postgres for this to work. If necessary, consider using the edit fields (set) node before this node to adjust the format as needed.

Update options#

Connection Timeout: The number of seconds to try to connect to the database.
Delay Closing Idle Connection: The number of seconds to wait before considering idle connections eligible for closing.
Query Batching: The way to send queries to the database:
- Single Query: A single query for all incoming items.
- Independently: Execute one query per incoming item of the execution.
- Transaction: Execute all queries in a transaction. If a failure occurs, Postgres rolls back all changes.
Output Columns: Choose which columns to output. You can select from a list of available columns or specify IDs using expressions.
Output Large-Format Numbers As: The format to output NUMERIC and BIGINT columns as:
- Numbers: Use this for standard numbers.
- Text: Use this if you expect numbers longer than 16 digits. Without this, numbers may be incorrect.
Replace Empty Strings with NULL: Whether to replace empty strings with NULL in input. This may be useful when working with data exported from spreadsheet software.

模板和示例#

使用查询参数#

在创建要在 Postgres 数据库上运行的查询时，您可以使用选项部分中的查询参数字段将数据加载到查询中。n8n 会清理查询参数中的数据，以防止 SQL 注入。

例如，您想根据电子邮件地址查找一个人。给定以下输入数据：

[
    {
        "email": "[email protected]",
        "name": "Alex",
        "age": 21 
    },
    {
        "email": "[email protected]",
        "name": "Jamie",
        "age": 33 
    }
]

您可以编写如下查询：

SELECT * FROM $1:name WHERE email = $2;

然后在查询参数中，提供要使用的字段值。您可以提供固定值或表达式。对于此示例，使用表达式，以便节点可以依次从每个输入项中提取电子邮件地址：

// users 是示例表名
{{ [ 'users', $json.email ] }} 

常见问题#

对于常见问题或疑问及建议的解决方案，请参考常见问题。

此页面是否

💬 微信

🚀 与作者交流

关注公众号

n8n实战笔记
📚 教程 💡 案例 🔧 技巧

添加微信

1对1 专业指导
⚡ 快答 🎯 定制 🚀 支持

添加后请直说来意，看到会回复。谢谢

Postgres 节点#

操作#

删除#

Delete options#

执行查询#

Execute Query options#

插入#

Insert options#

插入或更新#

Insert or Update options#

查询#

Select options#

更新#

Update options#

模板和示例#

相关资源#

使用查询参数#

常见问题#

🚀 与作者交流