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!

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

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

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

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

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

RETURNING exists also in Oracle PL/SQL and it is called OUTPUT in T-SQL. It may exist in other SQL variants.

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

;)

But would any of us have seen this blogpost without the clickbait title? Probably not

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

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

What SQL Textbook should I read?

The Postgres docs are some of the best reading you can do. Super digestible, great examples, and all around excellent for learning.

"Become a Select St*r!" (https://wizardzines.com/zines/sql/)

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

This sounds great!

I found reading the postgres documentation and playing around with each of the examples useful.

I highly recommend SQL for Smarties.

https://www.amazon.com/Joe-Celkos-SQL-Smarties-Programming/d...

Thanks, flagged it