SQL Server を PostgreSQL に変換

-- Written by IntoSQL from a SQL Server 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 orders (
    id integer GENERATED BY DEFAULT AS IDENTITY NOT NULL,
    customer_id integer NOT NULL,
    status varchar(10) DEFAULT 'pending' NOT NULL,
    total numeric(19,4) NOT NULL,
    items smallint DEFAULT 1 NOT NULL,
    ordered_at timestamp(0) NOT NULL,
    shipped_at timestamp(6),
    note text,
    details text,
    order_ref uuid DEFAULT (gen_random_uuid()::text) NOT NULL,
    total_with_tax numeric(12,2) GENERATED ALWAYS AS ((cast(total * (1.16) AS numeric(12,2)))::numeric(12,2)) STORED,
    CONSTRAINT orders_items_check CHECK (items >= 0),
    CONSTRAINT orders_check2 CHECK (status = 'cancelled' OR status = 'shipped' OR status = 'paid' OR status = 'pending'),
    CONSTRAINT orders_check3 CHECK (total >= (0))
);

CREATE TABLE customers (
    id integer GENERATED BY DEFAULT AS IDENTITY NOT NULL,
    name varchar(100) NOT NULL,
    email varchar(200) 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,
    row_version bytea
);

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

COPY orders (id, customer_id, status, total, items, ordered_at, shipped_at, note, details, order_ref) FROM stdin;
1	1	shipped	129.9000	3	2024-03-01T10:00:00.000000	2024-03-02T16:30:00.123456	\N	{"gift": false}	0b7a4c2e-3f1d-4e8a-9c55-1a2b3c4d5e6f
2	2	paid	54.0000	1	2024-03-02T11:15:00.000000	\N	Gift wrap 🎁	{"gift": true, "wrap": "red"}	1c8b5d3f-4a2e-4f9b-8d66-2b3c4d5e6f70
3	1	cancelled	12.5000	1	2024-03-03T09:45:00.000000	\N	Customer changed their mind	\N	2d9c6e4a-5b3f-4a0c-9e77-3c4d5e6f7081
4	4	shipped	980.0000	12	2024-03-05T02:10:00.000000	2024-03-07T08:00:00.000000	请小心轻放	{"fragile": true}	3e0d7f5b-6c4a-4b1d-8f88-4d5e6f708192
5	5	pending	45.7500	2	2024-03-08T17:20:00.000000	\N	Leave at the door; ring twice	\N	4f1e806c-7d5b-4c2e-9a99-5e6f708192a3
6	6	shipped	310.4000	5	2024-03-10T13:00:00.000000	2024-03-11T09:00:00.000000	Fragile: "glass"	{"fragile": true}	50f2917d-8e6c-4d3f-8baa-6f708192a3b4
7	8	paid	76.2000	2	2024-03-11T18:45:00.000000	\N	\N	\N	6103a28e-9f7d-4e40-9cbb-708192a3b4c5
8	2	shipped	19.9900	1	2024-03-12T08:00:00.000000	9999-12-31T23:59:59.999999	Shipped date set to the maximum by an old import	\N	7214b39f-a08e-4f51-8dcc-8192a3b4c5d6
9	3	shipped	64.0000	2	2024-03-14T15:30:00.000000	2024-03-15T10:00:00.000000	Two parcels	{"parcels": [1, 2]}	8325c4a0-b19f-4062-9edd-92a3b4c5d6e7
10	7	paid	1499.0000	1	2024-03-15T21:05:00.000000	\N	C:\\Users\\ravi\\invoice.pdf	\N	9436d5b1-c2a0-4173-8fee-a3b4c5d6e7f8
\.


-- Keys, indexes and constraints, after the rows so each builds once.
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);
CREATE UNIQUE INDEX customers_pkey ON customers (id);
CREATE UNIQUE INDEX customers_email ON customers (email);
ALTER TABLE orders ADD CONSTRAINT orders_pkey PRIMARY KEY USING INDEX orders_pkey;
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_fkey FOREIGN KEY (customer_id) REFERENCES customers (id) 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('orders', 'id'), LEAST(GREATEST(COALESCE((SELECT max(id) FROM orders)::numeric, 0) + 1, 1), 2147483647)::bigint, false);
SELECT setval(pg_get_serial_sequence('customers', 'id'), LEAST(GREATEST(COALESCE((SELECT max(id) FROM customers)::numeric, 0) + 1, 1), 2147483647)::bigint, false);
COMMENT ON TABLE customers IS 'People who bought at least once';