> 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.

But your battle-trained mined, injured from weird RDBMSs, is always expecting the worst, and you still open the docs, searching for the gotcha.

Or like my colleagues: find behaviour that 99 out of 100 db-people find intuitive weird because it's not like WhAt ThEy ArE uSeD tO

What are they used to?

Depends on the industry segment, but I find that MySQL/MariaDB users tend to be the worst for this.

All the RDBMS systems are guilty of it to some degree though.

Oracle

The actual Python "pit of success" is postgres.

Well it's not just the obvious behaviour, it seems like the only useful behaviour, just like the default is returning the number of affected rows (which for insert, would be the number of inserts). You know ahead of time how many items you're trying to insert, it would be completely pointless to return that.

> You know ahead of time how many items you're trying to insert, it would be completely pointless to return that.

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.

Is it? Returning the conflicts that weren't inserted seems more obviously useful to me.

The RETURNING semantics produce content of the resulting row after insertion. For the ones that have been skipped, this could be ill-defined. The content is not just the content of the row data that was supplied, but potentially server-generated content from sequences, row-level triggers, etc.

There's one HUGE upside for doing it the current way: autogenerated columns.

E.g.

    CREATE TABLE t (id SERIAL PRIMARY KEY, x INTEGER UNIQUE);

    INSERT INTO t (x) VALUES (42), (43) ON CONFLICT DO NOTHING RETURNING *;

This does EXACTLY the most useful thing if one of '42' or '43' conflicts.

> You know ahead of time how many items you're trying to insert, it would be completely pointless to return that.

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.

I've been looking for way to bulk insert for postgres, but with the ability to ignore any error for any single row (including FK/unique constraint/check constraint/post insert trigger/whatever) and after it finishes give me the list of the rows that failed to insert. I haven't found any way to do this yet. Anyone has any idea?