That said, I was unaware of the big discovery in the post: Doing INSERT ... RETURNING ... ON CONFLICT DO NOTHING, and passing an array of rows to insert, the returning clause will return only the values from the rows that were actually inserted. IMO this is the "obvious" behavior, but it is still worthwhile to know it works!
I think this is why people love Postgres so much. 99 times out of 100, the "obvious" behaviour turns out to be the behaviour that Postgres actually implements.
All the RDBMS systems are guilty of it to some degree though.
E.g.
CREATE TABLE t (id SERIAL PRIMARY KEY, x INTEGER UNIQUE);
INSERT INTO t (x) VALUES (42), (43) ON CONFLICT DO NOTHING RETURNING *;
In simple CRUD cases yes. In many other scenarios, you might not. If your insert is a select statement, with joins and clauses, you can easily insert too much data, or no data at all.
I often use "insert into mytable(...) select ... from ... where ...", and I know I only want one row inserted.
In such cases checking that exactly one row was inserted is a very nice safeguard against buggy code/query, or in the freak case someone messed with the DB.