Postgres Node Insert or Update Unique Column Name

[jzrts]

Hi,

When configuring the upsert node, if the selected table doesn’t have a column named ‘id’, the table can’t be used. It just shows the message “No column that can be used for matching found in Postgres.”

For an example, create these two tables, and then only table1 will be possible to use with the upsert operation.

CREATE TABLE table1 (
  id varchar(255),
  datetime timestamp,
  result BOOLEAN
);

CREATE TABLE table2 (
  key varchar(255),
  datetime timestamp,
  result BOOLEAN
);

Is there a way to make the Postgres node upsert operation key on columns named other than ‘id’?

Thanks!

[MutedJam]

Hi @jzrts, this is a good question. n8n uses a specific query to find columns suitable for matching. The idea is to show only columns with unique values, making me wonder why the id column in your first example would be offered here.

I’ll add this to our bug tracker for a closer look and fix. In the meantime, perhaps you want to create your table2 like below?

CREATE TABLE table2 (
  key varchar(255) UNIQUE,
  datetime timestamp,
  result BOOLEAN
);

After using the UNIQUE constraint, n8n will offer the column for matching as expected:

Alternatively you can always use the Execute Query operation offered by the PostgreSQL node to avoid the n8n logic and write your own upsert query.

[jan]

New version [email protected] got released which includes the GitHub PR 7975.