SQLite を PostgreSQL に変換

-- Written by IntoSQL from a SQLite dump. Load it with: psql -v ON_ERROR_STOP=1 -f <file>
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET synchronous_commit = off;
SET maintenance_work_mem = '512MB';

CREATE TABLE customers (
    id bigint GENERATED BY DEFAULT AS IDENTITY NOT NULL,
    name text NOT NULL,
    email text NOT NULL,
    country text DEFAULT 'US' NOT NULL,
    is_active boolean DEFAULT true NOT NULL,
    signed_up date,
    created_at timestamp(0) DEFAULT LOCALTIMESTAMP NOT NULL,
    CONSTRAINT customers_check1 CHECK (char_length(country) = 2)
);

CREATE TABLE orders (
    id bigint GENERATED BY DEFAULT AS IDENTITY NOT NULL,
    customer_id bigint NOT NULL,
    status text DEFAULT 'pending' NOT NULL,
    total numeric(10,2) NOT NULL,
    items integer DEFAULT 1 NOT NULL,
    ordered_at timestamp(0) NOT NULL,
    shipped_at timestamp(0),
    note text,
    receipt bytea,
    CONSTRAINT orders_check1 CHECK (status IN ('pending', 'paid', 'shipped', 'cancelled'))
);

COPY customers (id, name, email, country, is_active, signed_up, created_at) FROM stdin;
1	Ana Martínez	ana@example.com	MX	t	2023-01-15	2023-01-15 10:24:00
2	Zoë Müller	zoe@example.de	DE	t	2023-02-03	2023-02-03 08:11:42
3	José Peña	jose@example.es	ES	f	2023-03-21	2023-03-21 19:05:17
4	李雷	lilei@example.cn	CN	t	2023-05-09	2023-05-09 02:40:00
5	Noah O'Brien	noah@example.ie	IE	t	\N	2023-06-30 14:00:00
6	Amélie Laurent	amelie@example.fr	FR	t	2024-01-02	2024-01-02 09:30:00
7	Ravi Patel	ravi@example.in	IN	f	2024-02-29	2024-02-29 23:59:59
8	Sofia Rossi 🌻	sofia@example.it	IT	t	2024-04-12	2024-04-12 12:12:12
\.

COPY orders (id, customer_id, status, total, items, ordered_at, shipped_at, note, receipt) FROM stdin;
1	1	shipped	129.9	3	2024-03-01 10:00:00	2024-03-02 16:30:00	\N	\N
2	2	paid	54	1	2024-03-02 11:15:00	\N	Gift wrap 🎁	\N
3	1	cancelled	12.5	1	2024-03-03 09:45:00	\N	Customer changed their mind	\N
4	4	shipped	980	12	2024-03-05 02:10:00	2024-03-07 08:00:00	请小心轻放	\\x25504446
5	5	pending	45.75	2	2024-03-08 17:20:00	\N	Leave at the door;\nring twice	\N
6	6	shipped	310.4	5	2024-03-10 13:00:00	2024-03-11 09:00:00	Fragile: "glass"	\N
7	8	paid	76.2	2	2024-03-11 18:45:00	\N	\N	\N
8	2	paid	19.99	1	2024-03-12 08:00:00	\N	Paid at the counter	\N
9	3	shipped	64	2	2024-03-14 15:30:00	\N	Shipping date never recorded	\N
10	7	paid	1499	1	2024-03-15 21:05:00	\N	C:\\Users\\ravi\\invoice.pdf	\N
\.


-- Keys, indexes and constraints, after the rows so each builds once.
CREATE UNIQUE INDEX customers_pkey ON customers (id);
CREATE UNIQUE INDEX customers_email ON customers (email);
CREATE UNIQUE INDEX orders_pkey ON orders (id);
CREATE INDEX orders_idx_orders_customer ON orders (customer_id);
CREATE INDEX orders_idx_orders_status_date ON orders (status, ordered_at);
ALTER TABLE customers ADD CONSTRAINT customers_pkey PRIMARY KEY USING INDEX customers_pkey;
ALTER TABLE customers ADD CONSTRAINT customers_email UNIQUE USING INDEX customers_email;
ALTER TABLE orders ADD CONSTRAINT orders_pkey PRIMARY KEY USING INDEX orders_pkey;
ALTER TABLE orders ADD CONSTRAINT orders_fkey FOREIGN KEY (customer_id) REFERENCES customers (id) ON DELETE CASCADE NOT VALID;
DO $f$ BEGIN
    ALTER TABLE orders VALIDATE CONSTRAINT orders_fkey;
EXCEPTION WHEN foreign_key_violation THEN
    RAISE WARNING 'foreign key % has rows without a parent: it stays NOT VALID, and new rows are checked', 'orders_fkey';
END $f$;
SELECT setval(pg_get_serial_sequence('customers', 'id'), LEAST(GREATEST(COALESCE((SELECT max(id) FROM customers)::numeric, 0) + 1, 9), 9223372036854775807)::bigint, false);
SELECT setval(pg_get_serial_sequence('orders', 'id'), LEAST(GREATEST(COALESCE((SELECT max(id) FROM orders)::numeric, 0) + 1, 11), 9223372036854775807)::bigint, false);