BEGIN;

    INSERT INTO entities (name)
    (VALUES ('a'), ('b'), ('c') EXCEPT SELECT name FROM entities);

    SELECT id, name FROM entities WHERE name IN ('a', 'b', 'c');

    COMMIT;

Use WITH select id, name from entities where name in :data AS existing

Then return a UNION of existing and (INSERT into entities (name) :data except existing returning id, name)

I've actually seen the specific misunderstanding many times on forums and on IRC. I wonder if it'd be possible to change the syntax to something like "RETURNING [ ALL | NEW ] * | output_expression [ [ AS ] output_name ]" where when empty "NEW" would be used and be equivalent to the current semantics where "ALL" would include rows that were not inserted on conflict. "ALL" would have no different meaning on "ON CONFLICT DO UPDATE" or when no conflict resolution was specified.

I guess that would be possible, but I would be very surprised if that was so... Also, I would also expect writes to rows to be atomic (i.e. you wouldn't see a row with half-written data, or a row that has an `id` but not (yet) a `name`) - again, that kind of behaviour would be possible, but very surprising to the point of the DB being unusable.

Race conditions are always surprising. Fortunately, we have a simple remedy for that: transactions. :)

Suppose you have another connection and it runs DELETE FROM t WHERE id = (SELECT MAX(id) FROM t). If that winds up in the middle, it could screw things up for you. Is it likely to happen? No, but again... the remedy is simple. Also, the scenario you describe seems simple enough, but what happens when you bulk insert a few thousand rows?

By the way, with Postgres, there really is no way to not have a transaction. If you don't issue BEGIN, it treats the statement as a small transaction. So you're getting the same overhead on a single statement, if your plan was to somehow avoid the overhead.

If this makes you question the database's usability, I have some bad news for you: all ACID compliant databases work this way. The "atomic" unit is the transaction, not the connection. The consistency guarantees pertain to the transaction, not the connection. This is not a weird thing about Postgres, this is how they all work (except, of course, that MySQL can't always roll back aspects of a transaction, like DDL).

most people do read (sometimes even in another thread / other connection) -> transaction -> write. Also the basic transaction isolation in pg would still have these inconsistentsis of phantom/non repeatable reads and serialization anomalies.

basically yes you need transactions to guard against dirty reads, but most often you don't care for dirty data, because if you are not having financial data or data that needs to be taken with care, let's say you have a timeline with posts, you don't care if the last read was dirty and probably the user does not care anyway, he will just refresh the page once he needs more up to date data.

For now, there are optimizations planned for the query planner that would optimize this to a single query in the next releases.

Some commercial databases already do that.

INSERT RETURNING only returns the value of the changed rows, but it does not return anything if you redirect writes with triggers or child tables, including partitioning

Actually, I find SQL pretty nice. It's strongly typed in non-MySQL engines. It's pretty expressive, and can be highly optimized without having to update queries. Analytical features found in non-MySQL databases such as windows are also invaluable for many types of analysis.

The biggest issue I've ever had is that nesting queries when doing some analytical work can be a pain. WITH does handle this to some extent, but it doesn't seem quite as nice as something like PIG where results an "flow" through the query.

The other issue is transformations like pivot tables where you don't know the column names ahead of time. pg has extensions for that, but they aren't quite as nice as a native SQL solution would be.

    WITH
      input_rows (name) AS
        (VALUES ('a'), ('b'), ('c')),

      insert_rows AS
        (INSERT INTO entities (name)
           SELECT ir.name
             FROM input_rows AS ir
           ON CONFLICT DO NOTHING
           RETURNING id, name),

      -- returned when insert was successful
      SELECT 'new' AS src, i.id, i.name
        FROM insert_rows AS i
      UNION ALL
      -- return when there's a conflict
      SELECT 'existing' AS src, e.id, e.name
        FROM entities AS e
        JOIN input_rows USING (name);