-- A real custom COPY format: newline-delimited JSON, round trip.
LOAD 'test_copy_ndjson';

CREATE TABLE ndj (id int, name text, amount numeric, ok bool, ts date);
INSERT INTO ndj VALUES
	(1, 'hello', 1.25, true, '2026-01-31'),
	(2, 'with "quotes" and \ backslash', -0.5, false, '1999-12-31'),
	(3, NULL, NULL, NULL, NULL),
	(4, E'tab\there and newline\nthere', 1e6, true, '2000-02-29');

-- COPY TO: one JSON object per row
COPY ndj TO STDOUT WITH (FORMAT ndjson);

-- round trip through a file
COPY ndj TO '/tmp/ndj_test.ndjson' WITH (FORMAT ndjson);
CREATE TABLE ndj_back (LIKE ndj);
COPY ndj_back FROM '/tmp/ndj_test.ndjson' WITH (FORMAT ndjson);

-- the data must survive unchanged
SELECT count(*) AS roundtripped FROM ndj_back;
SELECT * FROM ndj EXCEPT ALL SELECT * FROM ndj_back;
SELECT * FROM ndj_back EXCEPT ALL SELECT * FROM ndj;

-- a column list must be honoured on both sides
COPY ndj (id, name) TO STDOUT WITH (FORMAT ndjson);

-- COPY FROM with a column list: missing keys become NULL
TRUNCATE ndj_back;
COPY ndj (id, name) TO '/tmp/ndj_partial.ndjson' WITH (FORMAT ndjson);
COPY ndj_back (id, name) FROM '/tmp/ndj_partial.ndjson' WITH (FORMAT ndjson);
SELECT id, name, amount IS NULL AS amount_null FROM ndj_back ORDER BY id;

DROP TABLE ndj, ndj_back;