5 ms·
Long as hell post with a clickbait title documenting how the author found out (1) one can insert multiple rows with a single INSERT statement; (2) the RETURNING
by oefrha 6y ago
Long as hell post with a clickbait title documenting how the author found out (1) one can insert multiple rows with a single INSERT statement; (2) the RETURNING clause. Spend your time elsewhere if you’re already aware; maybe pick up a SQL textbook if you aren’t...
This is not an uncharitable read btw. Actual quotes:
> The first thing I learned was how to insert multiple rows with a single INSERT statement: ...
> Scouring the docs I discovered the RETURNING clause of an INSERT statement.
- quasarj 6y agoI 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?
- biddlesby 6y agoI didn't know about RETURNING and enjoyed the post. It's a much nicer way to learn than via a textbook. A blog post doesn't have to be novel to have value. That said, I'm with you on the clickbait title. That really grinds my gears.
- oefrha 6y agoIf you never had a formal introduction to SQL, you can probably get a lot of value from a run-of-the-mill SQL textbook by scanning the table of contents and skimming the chapters/sections on topics new to you. (For the record, I learned SQL myself through fumbling. My first schema designs didn’t even have relations.) The problem with this blog post isn’t lack of novelty; rather, with the clickbait title, it piqued my interest but I ended up learning nothing, other than being reminded of my own terrible designs and queries back when I was a noob.
- badrequest 6y agoAll that is well and good, but RETURNING is a Postgres feature, not part of the SQL standard. I don't think a standard SQL textbook (especially one targeting something like MySQL/Sqlite) would have taught the author about it.
- fabian2k 6y agoI was quite surprised when I found out that RETURNING is actually a Postgres feature, and not standard. It just felt like such an obvious and extremely useful thing to have.
- AdrianB1 6y agoRETURNING exists also in Oracle PL/SQL and it is called OUTPUT in T-SQL. It may exist in other SQL variants.
- branko_d 6y ago> My first schema designs didn’t even have relations. I'm pretty sure your first schema designs had relations (tables). I can't speak for relationSHIPS (foreign keys) though... ;)
- devmunchies 6y agoAgreed. I would expect learning about more edge cases here on HN. These are basic PG features you can see when looking at the INSERT definition in the docs. I second what you said about reading a text book. I read through the entire PG documentation and a PG book my second year as an engineer and 7 years later its paid off big time. It’s one of those things where if you do it early, you get compounding benefits over your career.
- xiphias2 6y agoThanks, flagged it
- revskill 6y agoThe title is fine. The problem is structure of the post. If author puts actual that word with a story to follow then all is good.
- aaomidi 6y agoWhat SQL Textbook should I read?
- ant6n 6y ago"Become a Select St*r!" (https://wizardzines.com/zines/sql/ https://wizardzines.com/zines/sql/)
- 3pt14159 6y agoI found reading the postgres documentation and playing around with each of the examples useful.
- sgk284 6y agoThe Postgres docs are some of the best reading you can do. Super digestible, great examples, and all around excellent for learning.
- wiml 6y agoOnce you have a basic understanding of SQL, I found it really informative to go closer to the source, e.g. C. J. Date and Hugh Darwen's "A Guide to the SQL Standard", or perhaps other books by them. There's a lot of incomplete and misunderstood information floating around in blog tutorials or potboiler textbooks.
- aaomidi 6y agoThis sounds great!
- jmalicki 6y agoI highly recommend SQL for Smarties. https://www.amazon.com/Joe-Celkos-SQL-Smarties-Programming/dp/0123820227 https://www.amazon.com/Joe-Celkos-SQL-Smarties-Programming/d...