How to return ids of rows with conflicting values?

Question

I am looking to insert or update values in an SQLite database (version > 3.35) avoiding multiple queries. upsert along with returning seems promising :

CREATE TABLE phonebook2(
  name TEXT PRIMARY KEY,
  phonenumber TEXT,
  validDate DATE
);
INSERT INTO phonebook2(name,phonenumber,validDate)
  VALUES('Alice','704-555-1212','2018-05-08')
  ON CONFLICT(name) DO UPDATE SET
    phonenumber=excluded.phonenumber,
    validDate=excluded.validDate
  WHERE excluded.validDate>phonebook2.validDate RETURNING name;

This helps me track names corresponding to inserted/modified rows. How to find rows where phonebook2 values conflict with values upserted in above statement, but no insert or update happened due to where clause?

Answer 1

The RETURNING clause can't be used to get non-affected rows.

What you can do is execute a SELECT statement before the UPSERT:

WITH cte(name, phonenumber, validDate) AS (VALUES
  ('Alice', '704-555-1212', '2018-05-08'),
  ('Bob','804-555-1212', '2018-05-09')
) 
SELECT *
FROM phonebook2 p
WHERE EXISTS (
  SELECT *
  FROM cte c
  WHERE c.name = p.name AND c.validDate <= p.validDate
);

In the CTE you may include as many tuples as you want