How to upsert when using data from a sub-query (Postgres)


Adam Hauze

I have two tables:

  • assignments {recceptacleId, assignedCarrier}
  • rls_permissions {receptacleId, rlsUserId}

An assignment in this context is any receptacle to airline carrier relationship.

Whenever a new assignment comes into the assignments table, I'd like to upsert (insert if new row or update if it's an existing receptacle being assigned to a new airline carrier) my rls_permissions table.

The issue I'm having with upsert, specifically ON CONFLICT ON CONSTRAINT, is that my insert statement contains a sub-query for the data to be inserted and therefore I'm not sure how to write the DO UPDATE SET part of the statement

I've tried using 'excluded' to try and single out the assignedCarrier that I want to update based on the previous conflict however I keep receiving "ERROR: column excluded.receptacleId does not exist"

My pkey looks like this:

CREATE UNIQUE INDEX rls_permissions_pkey ON rls_permissions("receptacleId" text_ops);

Dummy data could be:

receptacleID assignedCarrier
aaaaaaaaaa00 AA

Where AA is "American Airlines"

INSERT INTO rls_permissions ("receptacleId","rlsUserId")
SELECT DISTINCT assignments."receptacleId", assignments."assignedCarrier"
FROM assignments
ON CONFLICT ON CONSTRAINT rls_permissions_pkey
DO UPDATE SET "rlsUserId" = (SELECT DISTINCT assignments."assignedCarrier"
FROM assignments
WHERE assignments."receptacleId" = excluded."receptacleId");

The excepted result is that if no conflict, the data returned from the sub-query is inserted into a new row on the permissions table.

If there is a conflict, I'd like to update ONLY the newly assigned carrier, and not update or insert a new line since that receptacle already exists.

Continue reading...