PostgreSQL のダンプを古いまたは新しい PostgreSQL へ移す

-- Written by IntoSQL from a PostgreSQL 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 TYPE order_status AS ENUM ('pending', 'paid', 'shipped', 'cancelled');
CREATE TABLE customers (
    id integer GENERATED BY DEFAULT AS IDENTITY NOT NULL,
    name text NOT NULL,
    email text NOT NULL,
    country varchar(2) DEFAULT 'US' NOT NULL,
    is_active boolean DEFAULT true NOT NULL,
    tags text[] DEFAULT '{}' NOT NULL,
    signed_up date,
    created_at timestamptz(6) DEFAULT CURRENT_TIMESTAMP NOT NULL
);

CREATE TABLE orders (
    id integer GENERATED BY DEFAULT AS IDENTITY NOT NULL,
    customer_id integer NOT NULL,
    status order_status DEFAULT 'pending' NOT NULL,
    total numeric(10,2) NOT NULL,
    items smallint DEFAULT 1 NOT NULL,
    ordered_at timestamp(6) NOT NULL,
    shipped_at timestamp(6),
    note text,
    details jsonb,
    delivery interval,
    CONSTRAINT orders_total_check CHECK (total >= (0))
);

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

COPY orders (id, customer_id, status, total, items, ordered_at, shipped_at, note, details, delivery) FROM stdin;
1	1	shipped	129.90	3	2024-03-01 10:00:00	2024-03-02 16:30:00	\N	{"gift": false}	1 day 06:30:00
2	2	paid	54.00	1	2024-03-02 11:15:00	\N	Gift wrap 🎁	{"gift": true, "wrap": "red"}	\N
3	1	cancelled	12.50	1	2024-03-03 09:45:00	\N	Customer changed their mind	\N	\N
4	4	shipped	980.00	12	2024-03-05 02:10:00	2024-03-07 08:00:00	请小心轻放	{"fragile": true}	2 days 05:50:00
5	5	pending	45.75	2	2024-03-08 17:20:00	\N	Leave at the door; ring twice	\N	\N
6	6	shipped	310.40	5	2024-03-10 13:00:00	2024-03-11 09:00:00	Fragile: "glass"	{"fragile": true}	20:00:00
7	8	paid	76.20	2	2024-03-11 18:45:00	\N	\N	\N	\N
8	2	shipped	19.99	1	2024-03-12 08:00:00	infinity	Shipped date set to infinity by an old import	\N	\N
9	3	shipped	64.00	2	2024-03-14 15:30:00	2024-03-15 10:00:00	Two parcels	{"parcels": [1, 2]}	18:30:00
10	7	paid	1499.00	1	2024-03-15 21:05:00	\N	C:\\Users\\ravi\\invoice.pdf	\N	\N
\.


-- Keys, indexes and constraints, after the rows so each builds once.
CREATE UNIQUE INDEX customers_email_key ON customers (email);
CREATE UNIQUE INDEX customers_pkey ON customers (id);
CREATE UNIQUE INDEX orders_pkey ON orders (id);
CREATE INDEX idx_orders_customer ON orders (customer_id);
CREATE INDEX idx_orders_status_date ON orders (status, ordered_at);
ALTER TABLE customers ADD CONSTRAINT customers_email_key UNIQUE USING INDEX customers_email_key;
ALTER TABLE customers ADD CONSTRAINT customers_pkey PRIMARY KEY USING INDEX customers_pkey;
ALTER TABLE orders ADD CONSTRAINT orders_pkey PRIMARY KEY USING INDEX orders_pkey;
ALTER TABLE orders ADD CONSTRAINT orders_customer_id_fkey FOREIGN KEY (customer_id) REFERENCES customers (id) NOT VALID;
DO $f$ BEGIN
    ALTER TABLE orders VALIDATE CONSTRAINT orders_customer_id_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_customer_id_fkey';
END $f$;
SELECT setval(pg_get_serial_sequence('customers', 'id'), LEAST(GREATEST(COALESCE((SELECT max(id) FROM customers)::numeric, 0) + 1, 1001), 2147483647)::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), 2147483647)::bigint, false);