7 ms·
I enjoyed trying to guess at what the "one word" was he was going to discover. I was mostly right! That said, I was unaware of the big discovery in the post: D
by quasarj 6y ago
I enjoyed trying to guess at what the "one word" was he was going to discover. I was mostly right!
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!
- nicoburns 6y ago> 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.
- egeozcan 6y agoBut your battle-trained mined, injured from weird RDBMSs, is always expecting the worst, and you still open the docs, searching for the gotcha.
- teraku 6y agoOr 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
- dkersten 6y agoWhat are they used to?
- fphhotchips 6y agoDepends 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.
- teraku 6y agoOracle
- alexbanks 6y agoThe actual Python "pit of success" is postgres.
- Kwantuum 6y agoWell 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.
- chinhodado 6y agoI'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?