SQLite or PostgreSQL: which database to choose

-- One file, two databases: runs unchanged in SQLite 3.35+ and PostgreSQL
CREATE TABLE products (
    sku   TEXT PRIMARY KEY,
    title TEXT NOT NULL,
    price INTEGER NOT NULL CHECK (price >= 0),
    stock INTEGER NOT NULL DEFAULT 0
);

-- Add a product or, if the SKU exists, update the price and add to the stock
INSERT INTO products (sku, title, price, stock)
VALUES ('A-100', 'Oak table', 24000, 3)
ON CONFLICT (sku) DO UPDATE
    SET price = excluded.price,
        stock = products.stock + excluded.stock
RETURNING sku, price, stock;
-- first run:  A-100 | 24000 | 3
-- second run: A-100 | 24000 | 6

-- The same INSERT with a word instead of a number
INSERT INTO products (sku, title, price)
VALUES ('C-300', 'Birch chair', 'free');

-- SQLite, ordinary table: the row is saved, price = 'free'
--   (CHECK passes too: in SQLite any text is "greater" than any number)
-- SQLite, table declared STRICT:
--   cannot store TEXT value in INTEGER column products.price
-- PostgreSQL:
--   invalid input syntax for type integer: "free"

# Move a table from SQLite to PostgreSQL. The table in PostgreSQL is created first:
#   CREATE TABLE products (id integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
#                          sku text UNIQUE NOT NULL, title text NOT NULL, price integer NOT NULL);

# 1. Export from SQLite to CSV
sqlite3 -header -csv app.db "SELECT id, sku, title, price FROM products" > products.csv

# 2. Load into PostgreSQL, keeping the same ids
psql "$DATABASE_URL" -c "\copy products (id, sku, title, price) FROM 'products.csv' CSV HEADER"

# 3. Move the id counter past the loaded rows,
#    or the next INSERT fails with "duplicate key value violates unique constraint"
psql "$DATABASE_URL" -c "SELECT setval(pg_get_serial_sequence('products', 'id'), max(id)) FROM products"