ON CONFLICT DO UPDATE has missing FROM-clause

Question

I have a simple table (id and name column, both unique), which I am importing a tab delimited CSV file.

I am running psql 9.5, and wanted to try out the new ON CONFLICT feature to update the name column if the ID already exists.

CREATE TEMP TABLE tmp_x AS SELECT * FROM repos LIMIT 0;
COPY tmp_x FROM '/Users/George/git-parser/repo_file' (format csv, delimiter E'\t');
INSERT INTO repos SELECT * FROM tmp_x
ON CONFLICT(name) DO UPDATE SET name = tmp_x.name;
DROP TABLE tmp_x;

I am getting this error:

SELECT 0
COPY 1
ERROR:  missing FROM-clause entry for table "tmp_x"
LINE 4: ON CONFLICT(name) DO UPDATE SET name = tmp_x.name;
                                               ^
Query failed
PostgreSQL said: missing FROM-clause entry for table "tmp_x"

Not too sure whats going wrong here.

Answer 1

If you look at the documentation of the ON CONFLICT clause, it says this about the "conflict action":

The SET and WHERE clauses in ON CONFLICT DO UPDATE have access to the existing row using the table's name (or an alias)

In your query, the target table is repos.

tmp_x, on the other hand, is the source of the data you are trying to insert, but the ON CONFLICT clause cannot "see" that - it is looking at a particular row that has been calculated and failed. Consider if you'd written something like this:

INSERT INTO repos SELECT max(foo_id) FROM tmp_x

Clearly, it wouldn't make sense for a row which failed to insert into repos to have access to any one row from tmp_x.

If there was no way of seeing the rejected data, the whole feature would be pretty useless, but if we read on:

... and to rows proposed for insertion using the special excluded table.

So instead, you need to access the magic table alias excluded, which contains the values which you tried to insert but got a conflict on, giving you this:

INSERT INTO repos SELECT * FROM tmp_x
ON CONFLICT(name) DO UPDATE SET name = excluded.name;

If it seems weird that an imaginary table name pops up for this purpose, consider that a similar thing happens when writing triggers, where you get OLD and NEW (depending on the kind of trigger you're writing).