How to pass value to ON CONFLICT clause that's not a column?

Basically, I'm trying to do multiple inserts in a single command in postgresql, where my ON CONFLICT has a per-insert parameter.

Here is the query (formatted for psycopg2):

INSERT INTO
    web_pages
    (url, starturl, netloc, distance, is_text, 
     priority, type, addtime, state)
VALUES
    (%(url)s, %(starturl)s, %(netloc)s, %(distance)s, %(is_text)s, 
      %(priority)s, %(type)s, %(addtime)s, %(state)s)
ON CONFLICT (url) DO
    UPDATE
        SET
            state           = EXCLUDED.state,
            starturl        = EXCLUDED.starturl,
            netloc          = EXCLUDED.netloc,
            is_text         = EXCLUDED.is_text,
            distance        = LEAST(EXCLUDED.distance, web_pages.distance),
            priority        = GREATEST(EXCLUDED.priority, web_pages.priority),
            addtime         = LEAST(EXCLUDED.addtime, web_pages.addtime)
        WHERE
        (
                web_pages.ignoreuntiltime < %(ignoreuntiltime)s
            AND
                web_pages.url = EXCLUDED.url
            AND
                (web_pages.state = 'complete' OR web_pages.state = 'error')
        )
    ;

I want to be able to pass multiple VALUES tuples, but %(ignoreuntiltime)s can vary per value-tuple.

Is there any way I can specify it as a additional parameter in the values tuple, or similar, for each row?

What I want is kind of:

```
INSERT INTO
web_pages
(url, starturl, netloc, distance, is_text,
priority, type, addtime, state, not_a_column)
VALUES
(%(url)s, %(starturl)s, %(netloc)s, %(distance)s, %(is_text)s,
%(priority)s, %(type)s, %(addtime)s, %(state)s, %(ignoreuntiltime)s)
....(more value tuples here)....
ON CONFLICT (url) DO
UPDATE
SET
state = EXCLUDED.state,
starturl = EXCLUDED.starturl,
netloc = EXCLUDED.netloc,
is_text = EXCLUDED.is_text,
distance = LEAST(EXCLUDED.distance, web_page

Not possible, because

There is currently no direct way in Postgres 9.6, because:

-
In the UPDATE, only the special EXCLUDED row is visible (in addition to the updated row). There is no FROM clause allowed to join in additional tables.

-
The EXCLUDED row is exactly the state of the would-be inserted row that was rejected by conflict. Exactly the columns of the table to be inserted into. Nothing more.

So there is no room for any additional columns not mentioned in the INSERT in the UPDATE branch of the UPSERT. Future version may allow more. Various additions have been discussed, but not yet implemented. It's a complex matter.
Clean workaround

I suggest this workaround using CTEs.

-
Provide rows with all columns you need in a free-standing VALUES expression. (CTE data in below example.)

-
Run INSERT ... ON CONFLICT ... DO UPDATE, but do not actually update any rows (WHERE FALSE). The desired side effect: excluded rows are locked anyway to make it safe for concurrent use.

With just ON CONFLICT ... DO NOTHING, conflicting rows are not locked and fair game for concurrent transactions. If there cannot be concurrent writes to the same rows of the same table, use this cheaper variant.

-
Run a separate UPDATE as part of the same command, where you can use all columns from the CTE data - and join in arbitrary additional tables, too, if needed.

Demo table:

CREATE TEMP TABLE tbl (tbl_id int PRIMARY KEY, note text);
INSERT INTO tbl VALUES
   (1, 'old1')
 , (2, 'old2');

Demo query:

WITH data (tbl_id, note) AS (
   VALUES
      (int '1', text 'update')   -- explicit type declarations for free standing VALUES
    , (2, 'no update')           -- going to exclude row in WHERE of UPDATE
    , (3, 'insert')              -- not going to INSERT the "note"
   )
, ins AS (
   INSERT INTO tbl AS t (tbl_id) -- "note" not inserted
   SELECT tbl_id FROM data
   ON     CONFLICT (tbl_id) DO UPDATE
   SET    note = NULL
   WHERE  FALSE                  -- never executed, but locks row.
   RETURNING t.tbl_id
   )
UPDATE tbl t
SET    note = d.note             -- now we can!
FROM   data d
LEFT   JOIN ins i USING (tbl_id)
WHERE  i.tbl_id IS NULL
AND    t.tbl_id = d.tbl_id
AND    d.note <> 'no update';    -- just to demonstrate use in WHERE

Result:

TABLE tbl ORDER BY tbl_id;

tbl_id |  note
--------+--------
      1 | update   -- updated
      2 | old2     -- not updated because of WHERE in UPDATE
      3 |          -- inserted without "note"

Related:

• How to include excluded rows in RETURNING from INSERT … ON CONFLICT

StackExchange Database Administrators Q#164133, answer score: 5