28 ms·
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 ins
by Kwantuum 6y ago
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.
- OJFord 6y agoIs it? Returning the conflicts that weren't inserted seems more obviously useful to me.
- saltcured 6y agoThe 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.
- ivoras 6y agoThere'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.
- xeonoex 6y ago> 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.
- magicalhippo 6y ago> 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.