PostgreSQL or MySQL: a detailed comparison and which one to choose

-- PostgreSQL: add to the stock, or create the item if it is new
CREATE TABLE stock (
    sku text PRIMARY KEY,
    qty integer NOT NULL CHECK (qty >= 0)
);

INSERT INTO stock (sku, qty) VALUES ('A-100', 5)
ON CONFLICT (sku) DO UPDATE SET qty = stock.qty + EXCLUDED.qty;

INSERT INTO stock (sku, qty) VALUES ('A-100', 3)
ON CONFLICT (sku) DO UPDATE SET qty = stock.qty + EXCLUDED.qty;

SELECT sku, qty FROM stock;   -- A-100 | 8

-- MySQL 8: the same, with ON DUPLICATE KEY
CREATE TABLE stock (
    sku VARCHAR(32) PRIMARY KEY,
    qty INT NOT NULL CHECK (qty >= 0)
);

INSERT INTO stock (sku, qty) VALUES ('A-100', 5) AS new
ON DUPLICATE KEY UPDATE qty = stock.qty + new.qty;

INSERT INTO stock (sku, qty) VALUES ('A-100', 3) AS new
ON DUPLICATE KEY UPDATE qty = stock.qty + new.qty;

SELECT sku, qty FROM stock;   -- A-100 | 8

-- PostgreSQL: one GIN index covers any key inside jsonb
CREATE TABLE orders (
    id      bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    details jsonb NOT NULL DEFAULT '{}'
);
CREATE INDEX orders_details_idx ON orders USING gin (details);

INSERT INTO orders (details)
VALUES ('{"delivery": "courier", "floor": 5}'), ('{"delivery": "pickup"}');

SELECT id FROM orders WHERE details @> '{"delivery": "courier"}';   -- 1

-- MySQL 8: a JSON field is indexed through a generated column
CREATE TABLE orders (
    id       BIGINT AUTO_INCREMENT PRIMARY KEY,
    details  JSON NOT NULL,
    delivery VARCHAR(20) AS (details->>'$.delivery') STORED,
    INDEX (delivery)
);

INSERT INTO orders (details)
VALUES ('{"delivery": "courier", "floor": 5}'), ('{"delivery": "pickup"}');

SELECT id FROM orders WHERE delivery = 'courier';   -- 1

-- PostgreSQL: a schema change inside a transaction can be rolled back
BEGIN;
ALTER TABLE stock ADD COLUMN reserved integer NOT NULL DEFAULT 0;
-- the migration went wrong: roll back, and the column is gone
ROLLBACK;

SELECT column_name FROM information_schema.columns
WHERE table_name = 'stock'
ORDER BY ordinal_position;   -- sku, qty

-- MySQL 8: ALTER TABLE commits the transaction by itself
START TRANSACTION;
ALTER TABLE stock ADD COLUMN reserved INT NOT NULL DEFAULT 0;
ROLLBACK;   -- too late: the column has already been added

SELECT column_name FROM information_schema.columns
WHERE table_schema = DATABASE() AND table_name = 'stock'
ORDER BY ordinal_position;   -- sku, qty, reserved