データベースを別のデータベースに変換

-- Written by IntoSQL from a MySQL 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 varchar(100) NOT NULL,
    email varchar(150) NOT NULL,
    country varchar(2) DEFAULT 'US' NOT NULL,
    is_active boolean DEFAULT true NOT NULL,
    signed_up date,
    created_at timestamptz(0) DEFAULT CURRENT_TIMESTAMP NOT NULL,
    CONSTRAINT customers_id_check CHECK (id >= 0)
);

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 varchar(255),
    updated_at timestamptz(0) DEFAULT CURRENT_TIMESTAMP NOT NULL,
    CONSTRAINT orders_id_check CHECK (id >= 0),
    CONSTRAINT orders_customer_id_check CHECK (customer_id >= 0),
    CONSTRAINT orders_status_check CHECK (status IN ('pending', 'paid', 'shipped', 'cancelled', '')),
    CONSTRAINT orders_items_check CHECK (items >= 0)
);

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+00:00
2	Zoë Müller	zoe@example.de	DE	t	2023-02-03	2023-02-03 08:11:42+00:00
3	José Peña	jose@example.es	ES	f	2023-03-21	2023-03-21 19:05:17+00:00
4	李雷	lilei@example.cn	CN	t	2023-05-09	2023-05-09 02:40:00+00:00
5	Noah O'Brien	noah@example.ie	IE	t	\N	2023-06-30 14:00:00+00:00
6	Amélie Laurent	amelie@example.fr	FR	t	2024-01-02	2024-01-02 09:30:00+00:00
7	Ravi Patel	ravi@example.in	IN	f	2024-02-29	2024-02-29 23:59:59+00:00
8	Sofia Rossi 🌻	sofia@example.it	IT	t	2024-04-12	2024-04-12 12:12:12+00:00
\.

COPY orders (id, customer_id, status, total, items, ordered_at, shipped_at, note, updated_at) FROM stdin;
1	1	shipped	129.90	3	2024-03-01 10:00:00	2024-03-02 16:30:00	\N	2024-03-02 16:30:00+00:00
2	2	paid	54.00	1	2024-03-02 11:15:00	\N	Gift wrap 🎁	2024-03-02 11:20:00+00:00
3	1	cancelled	12.50	1	2024-03-03 09:45:00	\N	Customer changed their mind	2024-03-03 10:00:00+00:00
4	4	shipped	980.00	12	2024-03-05 02:10:00	2024-03-07 08:00:00	请小心轻放	2024-03-07 08:00:00+00:00
5	5	pending	45.75	2	2024-03-08 17:20:00	\N	Leave at the door; ring twice	2024-03-08 17:20:00+00:00
6	6	shipped	310.40	5	2024-03-10 13:00:00	2024-03-11 09:00:00	Fragile: "glass"	2024-03-11 09:00:00+00:00
7	8	paid	76.20	2	2024-03-11 18:45:00	\N	\N	2024-03-11 18:46:00+00:00
8	2		19.99	1	2024-03-12 08:00:00	\N	Status lost in an old import	2024-03-12 08:00:00+00:00
9	3	shipped	64.00	2	2024-03-14 15:30:00	\N	Shipping date never recorded	2024-03-15 10:00:00+00:00
10	7	paid	1499.00	1	2024-03-15 21:05:00	\N	C:\\Users\\ravi\\invoice.pdf	2024-03-15 21:05:00+00:00
\.


-- Keys, indexes and constraints, after the rows so each builds once.
CREATE UNIQUE INDEX customers_pkey ON customers (id);
CREATE UNIQUE INDEX customers_uq_email ON customers (email);
CREATE UNIQUE INDEX orders_pkey ON orders (id);
CREATE INDEX orders_idx_customer ON orders (customer_id);
CREATE INDEX orders_idx_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_uq_email UNIQUE USING INDEX customers_uq_email;
ALTER TABLE orders ADD CONSTRAINT orders_pkey PRIMARY KEY USING INDEX orders_pkey;
ALTER TABLE orders ADD CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES customers (id) NOT VALID;
DO $f$ BEGIN
    ALTER TABLE orders VALIDATE CONSTRAINT fk_orders_customer;
EXCEPTION WHEN foreign_key_violation THEN
    RAISE WARNING 'foreign key % has rows without a parent: it stays NOT VALID, and new rows are checked', 'fk_orders_customer';
END $f$;
SELECT setval(pg_get_serial_sequence('customers', 'id'), LEAST(GREATEST(COALESCE((SELECT max(id) FROM customers)::numeric, 0) + 1, 1001), 9223372036854775807)::bigint, false);
COMMENT ON TABLE customers IS 'People who bought at least once';
SELECT setval(pg_get_serial_sequence('orders', 'id'), LEAST(GREATEST(COALESCE((SELECT max(id) FROM orders)::numeric, 0) + 1, 5001), 9223372036854775807)::bigint, false);
CREATE FUNCTION orders_updated_at_on_update() RETURNS trigger LANGUAGE plpgsql AS $f$
BEGIN
    IF NEW IS DISTINCT FROM OLD AND NEW.updated_at IS NOT DISTINCT FROM OLD.updated_at THEN
        NEW.updated_at := CURRENT_TIMESTAMP;
    END IF;
    RETURN NEW;
END
$f$;
CREATE TRIGGER orders_updated_at_on_update BEFORE UPDATE ON orders FOR EACH ROW EXECUTE FUNCTION orders_updated_at_on_update();