Insert into PostgreSQL from Spreadsheet (Excel)

[fdavidg]

I am having a terrible time INSERTING data into a PostgreSQL 15 database table from a Spreadsheet (Excel). I have double and triple checked the columns in the spreadsheet versus the columns in the database table (all are defined as text). Not only do I not understand the error message, but I do understand the fix. Where is ‘Data Mode’? How do I pull up ‘Define Below’ ?

I have attached three screenshots (I hope!). One of the table column names. One of the spreadsheet column names. And one of the workflow execution from n8n.

What is the error message (if any)?

ERROR: Column to match on not found in input item. Add a column to match on or set the ‘Data Mode’ to ‘Define Below’ to define the value to match on.

Problem in node ‘Postgres2 - ArbitrageHero‘

Column to match on not found in input item. Add a column to match on or set the ‘Data Mode’ to ‘Define Below’ to define the value to match on.

Please share your workflow

{
  "meta": {
    "instanceId": "85c03f9b374b262810971650276228ca4f039796d3adb26124a66ecd5a729f18"
  },
  "nodes": [
    {
      "parameters": {
        "fileSelector": "D:/groceries.xlsx"
      },
      "id": "a6521194-94e8-4597-9995-dfe10fbfd675",
      "name": "Read Binary Files",
      "type": "n8n-nodes-base.readBinaryFiles",
      "typeVersion": 1,
      "position": [
        -2520,
        800
      ]
    },
    {
      "parameters": {
        "operation": "upsert",
        "schema": {
          "__rl": true,
          "mode": "list",
          "value": "public"
        },
        "table": {
          "__rl": true,
          "value": "vc_groceries",
          "mode": "list",
          "cachedResultName": "vc_groceries"
        },
        "columns": {
          "mappingMode": "defineBelow",
          "value": {},
          "matchingColumns": [],
          "schema": [
            {
              "id": "Supplier Image",
              "displayName": "Supplier Image",
              "required": false,
              "defaultMatch": false,
              "display": true,
              "type": "string",
              "canBeUsedToMatch": false,
              "removed": false
            },
            {
              "id": "Supplier URL",
              "displayName": "Supplier URL",
              "required": false,
              "defaultMatch": false,
              "display": true,
              "type": "string",
              "canBeUsedToMatch": false,
              "removed": false
            },
            {
              "id": "Supplier Title",
              "displayName": "Supplier Title",
              "required": false,
              "defaultMatch": false,
              "display": true,
              "type": "string",
              "canBeUsedToMatch": false,
              "removed": false
            },
            {
              "id": "Amazon Image",
              "displayName": "Amazon Image",
              "required": false,
              "defaultMatch": false,
              "display": true,
              "type": "string",
              "canBeUsedToMatch": false,
              "removed": false
            },
            {
              "id": "Amazon URL",
              "displayName": "Amazon URL",
              "required": false,
              "defaultMatch": false,
              "display": true,
              "type": "string",
              "canBeUsedToMatch": false,
              "removed": false
            },
            {
              "id": "Amazon Title",
              "displayName": "Amazon Title",
              "required": false,
              "defaultMatch": false,
              "display": true,
              "type": "string",
              "canBeUsedToMatch": false,
              "removed": false
            },
            {
              "id": "ASIN",
              "displayName": "ASIN",
              "required": false,
              "defaultMatch": false,
              "display": true,
              "type": "string",
              "canBeUsedToMatch": false,
              "removed": false
            },
            {
              "id": "Barcode",
              "displayName": "Barcode",
              "required": false,
              "defaultMatch": false,
              "display": true,
              "type": "string",
              "canBeUsedToMatch": false,
              "removed": false
            },
            {
              "id": "Cost",
              "displayName": "Cost",
              "required": false,
              "defaultMatch": false,
              "display": true,
              "type": "string",
              "canBeUsedToMatch": false,
              "removed": false
            },
            {
              "id": "Price",
              "displayName": "Price",
              "required": false,
              "defaultMatch": false,
              "display": true,
              "type": "string",
              "canBeUsedToMatch": false,
              "removed": false
            },
            {
              "id": "Profit",
              "displayName": "Profit",
              "required": false,
              "defaultMatch": false,
              "display": true,
              "type": "string",
              "canBeUsedToMatch": false,
              "removed": false
            },
            {
              "id": "ROI",
              "displayName": "ROI",
              "required": false,
              "defaultMatch": false,
              "display": true,
              "type": "string",
              "canBeUsedToMatch": false,
              "removed": false
            },
            {
              "id": "VAT",
              "displayName": "VAT",
              "required": false,
              "defaultMatch": false,
              "display": true,
              "type": "string",
              "canBeUsedToMatch": false,
              "removed": false
            },
            {
              "id": "Fees",
              "displayName": "Fees",
              "required": false,
              "defaultMatch": false,
              "display": true,
              "type": "string",
              "canBeUsedToMatch": false,
              "removed": false
            },
            {
              "id": "Fees VAT",
              "displayName": "Fees VAT",
              "required": false,
              "defaultMatch": false,
              "display": true,
              "type": "string",
              "canBeUsedToMatch": false,
              "removed": false
            },
            {
              "id": "Sales Rank",
              "displayName": "Sales Rank",
              "required": false,
              "defaultMatch": false,
              "display": true,
              "type": "string",
              "canBeUsedToMatch": false,
              "removed": false
            },
            {
              "id": "Estimated Sales",
              "displayName": "Estimated Sales",
              "required": false,
              "defaultMatch": false,
              "display": true,
              "type": "string",
              "canBeUsedToMatch": false,
              "removed": false
            },
            {
              "id": "Amazon on the listing",
              "displayName": "Amazon on the listing",
              "required": false,
              "defaultMatch": false,
              "display": true,
              "type": "string",
              "canBeUsedToMatch": false,
              "removed": false
            },
            {
              "id": "Category",
              "displayName": "Category",
              "required": false,
              "defaultMatch": false,
              "display": true,
              "type": "string",
              "canBeUsedToMatch": false,
              "removed": false
            }
          ]
        },
        "options": {}
      },
      "id": "efed37a3-71e0-495a-b225-2f63c9f7e706",
      "name": "Postgres2 - ArbitrageHero",
      "type": "n8n-nodes-base.postgres",
      "typeVersion": 2.2,
      "position": [
        -2100,
        800
      ],
      "credentials": {
        "postgres": {
          "id": "qAOKAyBJUn4Pi3k3",
          "name": "Postgres2 ArbitrageHero"
        }
      }
    },
    {
      "parameters": {
        "options": {}
      },
      "id": "4fe9113a-4ff0-4f54-b87f-869c26aba965",
      "name": "Spreadsheet File1",
      "type": "n8n-nodes-base.spreadsheetFile",
      "typeVersion": 1,
      "position": [
        -2300,
        800
      ]
    },
    {
      "parameters": {},
      "id": "dffdfa8e-ee1e-4a33-953b-64d1b91a83d9",
      "name": "Execute Workflow Trigger",
      "type": "n8n-nodes-base.executeWorkflowTrigger",
      "typeVersion": 1,
      "position": [
        -2700,
        800
      ]
    }
  ],
  "connections": {
    "Read Binary Files": {
      "main": [
        [
          {
            "node": "Spreadsheet File1",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Spreadsheet File1": {
      "main": [
        [
          {
            "node": "Postgres2 - ArbitrageHero",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Execute Workflow Trigger": {
      "main": [
        [
          {
            "node": "Read Binary Files",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}
![n8n workflow after execution_2023_08_06|690x388](upload://tN9b7PQ4SxwE4XkeHkDlzHamO7B.png)
![PostgreSQL table columns_2023_08_06|690x388](upload://c5VchFV47hPi9oXnW8AHnAXbeke.png)
![Spreadsheet (Excel) columns_2023_08_06|690x388](upload://bSfXN7ODCWciTjlqfYs71j9WZLn.png)

Share the output returned by the last node

There is no output from the workflow. See the attached screenshot.

Information on your n8n setup

• n8n version: 0.236.3
• Database (default: SQLite): PostgreSQL 15
• n8n EXECUTIONS_PROCESS setting (default: own, main): default
• Running n8n via (Docker, npm, n8n cloud, desktop app): npm
• Operating system: Windows 10 Pro

[giulioandreini]

Hi @fdavidg ,
it looks like the Postgres node can’t find any good field to be used for matching the rows in the table.
We’re reviewing your problem internally I’ll come back with more info soon.

In the meantime, could you make a test using the “Insert” operation and check if you see the list of available columns in “Values to Send”? (Mapping Column Mode must be set to Map Each Column Manually).

[milorad]

Hey @fdavidg,
sorry to hear you are having trouble with the node.
@giulioandreini is right, there seems to be an issue with reading your database schema (getting the list of fields from the table). It’s hard to tell from the screenshots but it could come a few sources:

• DB credentials lacking rights
• Table doesn’t have a column that can be used to match (primary key or a column with a UNIQUE constraint)
• A bug in the node

It would be helpful if you could provide the structure of the table you are trying to insert to here (preferably a CREATEstatement) and also screenshots of your browser console and network tabs in the developer tools, since more info on these kinds of errors usually end up there. Let us know if you need any help accessing the developer tools and the mentioned tabs.

[fdavidg]

giulioandreini & milorad,

I love this stuff! Getting things to work correctly is my thing, too!

I think I know what you are after, but I am not quite sure. You want me to open the Developer Tools when I am running the workflow and screenshot the network tab. correct?

How can I provide you with a table structure I am trying to INSERT to using a CREATE statement? I created the table under the public schema using pgAdmin.

Could you be more specific.

Onward, just let me know.

Frank Gunseor

[fdavidg]

I took a ‘shot’ (a screenshot, that is) at what I think you were after. If not you can send me more specifics.

Frank

[milorad]

Hey @fdavidg,
sorry, should’ve been more clear about this but thanks for your patience
Anyway, the screenshot you sent with the network tab is pretty much correct. We are interested in the request that starts with get-mapping-fields/but it would be good if you can post another one where we can see response from that request. Something like this:

This will show us if the node has managed to successfully fetch your table schema.
If you are wondering, this request is sent when you select/change table in the Table dropdown of your Postgres node.

[fdavidg]

I think I got what you want/need. See attached screenshot.

Let me know what’s next.

Thank you.

Frank

[milorad]

Ok,
that shows us something
As you can see in the fields list, each field has a canBeUsedToMatch property which tells n8n if that field can be used to identify rows in your table. Now, I don’t see the whole list but if all fields have this property set to false, then there is no field that can be used to match, and the message you are seeing in your node details is correct.
Now we need to check why is this so.
In order for a field to be used for matching it needs to be a primary key or it has to be set as unique in your DB schema.
Can you check if your groceries table has a primary key or a UNIQUE constraint?
if you don’t have any other way to check this, this query should list such columns from the table:

SELECT DISTINCT a.attname 
FROM pg_index i
JOIN pg_attribute a ON a.attrelid = i.indrelid AND a.attnum = ANY(i.indkey)
WHERE i.indrelid = quote_ident('vc_groceries')::regclass
  AND (i.indisprimary OR i.indisunique);

Do you have a way to run it and post the results here?

[fdavidg]

Hi Milorad,

Here is a screen shot of the query and the results:

All of the canBeUsedToMatch property fields were false. I just had to cut the screensho to make it readable.

It has been a long time since I was db Admin and that was with Micro$oft SQLServer, so this is a totally new learning experience.

Thank you for your patience and all your help.

Frank

[milorad]

Hey @fdavidg,
sorry for late reply but somehow I have missed your message.
So that confirms the theory that no column in the table is suited to be used for matching. I guess the quickest way to solve this is to declare one of the columns to be a primary key. Choose the columns that could be used to identify each row in your table (something unique) and you can follow this guide (notice the sections Configure PRIMARY KEY Constraint on Existing Tables and Define Primary Keys using pgAdmin) to turn it into a primary key.
Then, opening your node details again should show a matching column dropdown with your primary key column.
Let us know how this went.

[fdavidg]

Hello Milorad,

I added an “id” column to my spreadsheet and followed the guide to make it a primary key. Now I get a drop down and can select the column to match on, but I still get the same error. Does column order matter? The “id” column is of course the last column. The database is currently empty, so I am trying to add this new information to it. Does that matter? I have checked and double checked the column heading and they are the same in the spreadsheet and database. After I added the “id” column to the spreadsheet I saved the spreadsheet as groceries2.xlsx, but it seems weird that it does not show up in the input table to the PostgreSQL node. Do I need to clear the whole workflow somehow to get it to show up?

I have attached a screenshot from my latest executiono below:

Thank you for your help! Sorry for the long issue.

Frank

[fdavidg]

Hi Milorad,

Is there anything I could try to solve this?

Does the order of columns in the spreadsheet have to exactly match the order in the n8n PostgreSQL dB node?

I understand that the Headings have to be exactly the same. Can the Headings contain spaces like mine? Are there other limitations to the PostgreSQL dB node?

Thank you.

Frank

[giulioandreini]

Hi @fdavidg,
I suggest you use the Mapping Column Mode “Map Each Column Manually”.
In this way, you have more control on the field mapping and it is also easier to test. It also doesn’t require the field names to match, so it is easier.

Here’s a demo workflow to show how it works:

Hope it helps, let me know if you need more support
Best