Skip to main content

Insert Table

Inserts data into a PostgreSQL table.

Common Properties​

  • Name - The custom name of the node.
  • Color - The custom color of the node.
  • Delay Before (sec) - Waits in seconds before executing the node.
  • Delay After (sec) - Waits in seconds after executing node.
  • Continue On Error - Automation will continue regardless of any error. The default value is false.
info

If the ContinueOnError property is true, no error is caught when the project is executed, even if a Catch node is used.

Inputs​

  • Connection Id - The unique identifier of the database connection to use.
  • Transaction Id - The unique identifier of the transaction (optional).
  • Table Name - The name of the table to insert data into.
  • Table Data - The data to insert, provided as a table structure.

Options​

  • Ignore Conflicts - Ignores unique constraint violations without throwing an error.
  • Replace - Replaces existing rows when conflicts occur (conflicting column name must be specified).
  • Conflict Column Name - The name of the column to check for conflicts when using Replace.
  • Conflict Constraint Name - The name of the constraint to check for conflicts when using Replace.
  • Column Names with Special Characters - Adds quotes to column names.
  • Credentials - Database credentials, used instead of a Connection Id when you do not need a persistent connection.

Output​

  • None. The node produces no output variable. The message passes through unchanged, so you can chain another Insert Table, an Execute Non Query, or a Commit Transaction after it.

How It Works​

The Insert Table node inserts data into a PostgreSQL table using the provided parameters. When executed, the node:

  1. Validates the provided connection ID and retrieves the connection
  2. Optionally retrieves the transaction if a transaction ID is provided
  3. Creates an insert command based on the table data and options
  4. Executes the insert command for each row in the table data

Requirements​

  • A valid PostgreSQL connection must be established
  • The table name must be valid and exist in the database
  • The table data must be properly formatted
  • If using transactions, a valid transaction ID must be provided

Error Handling​

The node will return specific errors in the following cases:

ConditionWhat causes itHow to fix it
Empty or invalid Connection IdThe Connect node did not run, or the message variable holding the ID has a different nameWire a Connect node upstream, or supply Credentials on this node instead
Connection Id not foundThe connection was closed by a Disconnect node earlier in the flow, or the robot restartedMove the Disconnect after the insert; connections do not survive a robot restart
Empty or invalid Table NameTable Name was left blank, or a Message variable resolved to emptySet Table Name as a literal, or check the upstream variable is populated
Invalid table data formatRows are positional arrays, or the shape uses header instead of columnsUse {columns: [...], rows: [{key: value}]} with row keys matching column names
Transaction Id not foundCommit Transaction already ran, or the ID came from a different branchKeep Start, Insert and Commit on the same branch
Both Conflict Column Name and Conflict Constraint Name providedReplace accepts exactly one conflict targetClear whichever one you do not need
Both Conflict Column Name and Conflict Constraint Name emptyReplace is enabled but has no conflict targetSet one of the two, or turn Replace off
Ignore Conflicts and Replace both selectedThe two map to DO NOTHING and DO UPDATE, which are mutually exclusivePick one

Usage Examples​

Example 1: Load a CSV into a Table​

- Connect -> conn_id
- CSV To Data Table (customers.csv) -> table
- Insert Table:
  - Connection Id: conn_id
  - Table Name: customers
  - Table Data: table
- Disconnect (conn_id)

In the SDK:

.then('a06926', 'Robomotion.PostgreSQL.Insert', 'Load Customers', {
  connectionId: Message('conn_id'),
  databaseTable: Custom('customers'),
  table: Message('table')
})

Example 2: Insert Without a Connect Node​

For a single insert, credentials on the node itself avoid the Connect and Disconnect pair:

.then('b17c34', 'Robomotion.PostgreSQL.Insert', 'Log Run', {
  databaseTable: Custom('run_log'),
  table: Message('table'),
  optCredentials: Credential({ vaultId: 'vault-uuid', itemId: 'item-uuid' })
})

Example 3: Make a Re-run Safe with Ignore Conflicts​

Rows that already exist are skipped rather than failing the flow. This is ON CONFLICT DO NOTHING:

.then('c28d45', 'Robomotion.PostgreSQL.Insert', 'Insert New Only', {
  connectionId: Message('conn_id'),
  databaseTable: Custom('orders'),
  table: Message('table'),
  optIgnoreConflicts: true
})

Example 4: Upsert on a Conflict Column​

Existing rows are updated instead of skipped. This is ON CONFLICT (email) DO UPDATE:

.then('d39e56', 'Robomotion.PostgreSQL.Insert', 'Upsert Customers', {
  connectionId: Message('conn_id'),
  databaseTable: Custom('customers'),
  table: Message('table'),
  optReplace: true,
  optConflictColumnName: Custom('email')
})

Example 5: Insert Two Tables in One Transaction​

Either both inserts land or neither does:

.then('e40f67', 'Robomotion.PostgreSQL.Start', 'Begin', {
  connectionId: Message('conn_id'),
  outTransactionId: Message('trx_id')
})

.then('f51a78', 'Robomotion.PostgreSQL.Insert', 'Insert Orders', {
  connectionId: Message('conn_id'),
  transactionId: Message('trx_id'),
  databaseTable: Custom('orders'),
  table: Message('orders_table')
})

.then('a62b89', 'Robomotion.PostgreSQL.Insert', 'Insert Order Lines', {
  connectionId: Message('conn_id'),
  transactionId: Message('trx_id'),
  databaseTable: Custom('order_lines'),
  table: Message('lines_table')
})

.then('b73c90', 'Robomotion.PostgreSQL.Commit', 'Commit', {
  transactionId: Message('trx_id')
})

Usage Notes​

  • The Table Data input should be provided as a properly formatted table structure
  • When using the Replace option, either Conflict Column Name or Conflict Constraint Name must be specified, but not both
  • The Ignore Conflicts and Replace options cannot be used simultaneously
  • When using transactions, the transaction must be committed using the Commit Transaction node
  • The Column Names with Special Characters option should be used when column names contain special characters

Tips​

  • This node's properties have no in prefix. They are connectionId, transactionId, databaseTable and table. The otherwise identical Oracle Insert Table node uses inConnectionId, inTransactionId, inDatabaseTable and inTable. Copying a node between the two packages therefore needs the property names changed, not just the node type
  • Ignore Conflicts, Replace and Column Names with Special Characters are real booleans. Write optIgnoreConflicts: true, not Custom('true'). Conflict Column Name and Conflict Constraint Name are values and do take Custom()
  • Ignore Conflicts is what makes a scheduled import safe to re-run after a partial failure, because the rows that already landed are skipped instead of raising a unique-violation
  • Replace needs a conflict target that is actually backed by a unique index or constraint; a plain column with duplicates in it will not work
  • The table shape is {columns: [...], rows: [{key: value}]} with row keys matching the column names, the same shape every table node in Robomotion uses. Column names must match the database columns
  • Credentials come from a Database vault item, which carries the host, port, database name, user and password together, so none of those are separate node properties
  • The package pools up to 20 connections. A long loop that calls Connect on every iteration will exhaust the pool, so connect once outside the loop
  • For anything that is not a straight row insert, such as an UPDATE with a WHERE clause, use Execute Non Query instead