Append tables with a source column

CREATE TEMP TABLE t1 AS SELECT * FROM (VALUES (1, 'a'), (2, 'b')) t(id, v);
CREATE TEMP TABLE t2 AS SELECT * FROM (VALUES (2, 'b'), (3, 'c')) t(id, v);
COPY t1 TO '<your_scratch_dir>/tag_one.csv' (HEADER);
COPY t2 TO '<your_scratch_dir>/tag_two.csv' (HEADER);
-- tables: a literal for each; the row (2, b) is in both and stays twice
CREATE TEMP TABLE from_tables AS
SELECT 't1' AS source, * FROM t1 UNION ALL BY NAME SELECT 't2' AS source, * FROM t2;
-- files: the reader adds the path of the file each row came from (filename = true); only its name is kept
CREATE TEMP TABLE from_files AS
SELECT parse_filename(filename) AS source, id, v
FROM read_csv(['<your_scratch_dir>/tag_one.csv', '<your_scratch_dir>/tag_two.csv'], filename = true);
SELECT * FROM from_tables ORDER BY source, id;
SELECT * FROM from_files ORDER BY source, id;
SELECT CASE WHEN ((SELECT list((source, id) ORDER BY source, id) = [('t1', 1), ('t1', 2), ('t2', 2), ('t2', 3)] FROM from_tables)
  AND (SELECT list((source, id, v) ORDER BY source, id) = [('tag_one.csv', 1, 'a'), ('tag_one.csv', 2, 'b'), ('tag_two.csv', 2, 'b'), ('tag_two.csv', 3, 'c')] FROM from_files)
  AND (SELECT list(column_name) FROM (DESCRIBE from_tables)) = ['source', 'id', 'v']) THEN 'as expected' ELSE error('each of the 4 rows should name the table or file it came from, the row found in both tables should appear once for each, and the source column should come first') END AS verdict;