Error Saving Chat JSON Output from AI Agent to PostgreSQL in n8n

Below is one way to solve this using the Postgres node to insert your chat data with metadata. The idea is to map each field (chat_id, user_input, ai_output, timestamp) from your JSON to the corresponding columns in your Postgres table.

Steps to Fix the Issue

1. Transform Your JSON if Needed
   Ensure the output from your AI Agent node is a plain object (or an array of objects) where each key matches a column name in your database. If necessary, use a Set node to rename or extract fields so that the names align with your table columns.

2. Choose the Right Operation
   Use the Insert operation on the Postgres node. This operation will allow you to feed your structured data into the table.

3. Mapping the Columns
   In the Postgres node:

   • Set Mapping Column Mode to either Map Automatically (if your field names and column names match exactly) or Map Each Column Manually.
   • When mapping manually, assign:
     • The value of chat_id to your database’s chat_id column.
     • The value of user_input to your designated column (for example, user_input).
     • The value of ai_output to your column (for example, ai_output).
     • The value of timestamp to a column (for example, named timestamp or created_at).

   This ensures that the JSON properties are broken out into individual columns in your table.

4. Optionally, Use Query Parameters
   If you prefer using a custom SQL query, change the Operation to Execute Query and write an INSERT query using query parameters. For example:

   SQLCopied
   INSERT INTO chat_history (chat_id, user_input, ai_output, timestamp)
   VALUES ($1, $2, $3, $4);

   Then in the Query Parameters field, provide the values with expressions:

   JSCopied
   {{ $json.chat_id }}, {{ $json.user_input }}, {{ $json.ai_output }}, {{ $json.timestamp }}

   This way, n8n will sanitize your input automatically and prevent SQL injection.

5. Make Sure Your Table is Ready
   Double-check your Postgres table schema to ensure it has columns that match the data you want to store (including data types such as a timestamp column to store the timestamp value).

Final Workflow Considerations

• Data Transformation:
  If the JSON from the AI Agent node includes additional nested data or if the structure doesn’t match your table, perform any necessary transformation with a Set node or Code node before passing it to the Postgres node.

• Error Handling:
  After configuring the mapping, run your workflow. If errors still occur, check the execution log to ensure that the values being passed match the expected column types (e.g., the timestamp is in the proper format).

By following these steps, you can save the conversations along with their timestamps and any other extra metadata in your Postgres database.