Detect column types from the data

CREATE TABLE raw AS SELECT * FROM (VALUES
  ('1',    '2026-01-03', '1.5',  'x',  '4.5', 'o-1'),
  ('22',   '2026-02-10', '2',    '7',  '10',  'o-2'),
  (NULL,   NULL,         '3.25', NULL, NULL,  NULL)
) t(whole, day, fraction, word, "unit price", "order");
-- a second table with no date column, where a type that is absent must not stop the recast, and a column with no value at all,
-- which stays VARCHAR
CREATE TABLE raw2 AS SELECT * FROM (VALUES ('1', '1.5', 'a', NULL::VARCHAR), ('2', '2', 'b', NULL)) t(n, price, label, note);
-- a table with no rows has no value to detect a type from, so it recasts to itself, its columns as they are
CREATE TABLE raw3 (code VARCHAR, qty VARCHAR);
-- a table of the same name in another schema, sharing a column name, must not add a column to the detection
CREATE SCHEMA other;
CREATE TABLE other.raw2 (label VARCHAR);
-- DuckDB 2.0.0 has no quote-identifier function, so a name is quoted by doubling its quotes
CREATE TEMP MACRO quoted(name) AS '"' || replace(name, '"', '""') || '"';
-- one table macro detects any table by name: every column is unpivoted as text, nulls kept so a column with no value stays
-- (as VARCHAR), and each is matched to its position among the table's columns in the current database and schema only
-- a value fits BIGINT only if casting it and printing it again gives the same text (TRY_CAST('1.5' AS BIGINT) is 2)
CREATE TEMP MACRO detect_types(tbl) AS TABLE
SELECT c.column_index, u.col,
       CASE WHEN count(u.v) = 0 THEN 'VARCHAR'
            WHEN count(*) FILTER (WHERE TRY_CAST(u.v AS BIGINT)::VARCHAR = u.v) = count(u.v) THEN 'BIGINT'
            WHEN count(TRY_CAST(u.v AS DATE)) = count(u.v) THEN 'DATE'
            WHEN count(TRY_CAST(u.v AS DOUBLE)) = count(u.v) THEN 'DOUBLE'
            ELSE 'VARCHAR' END AS detected_type
FROM (SELECT * FROM (SELECT COLUMNS(*)::VARCHAR FROM query_table(tbl))
      UNPIVOT INCLUDE NULLS (v FOR col IN (COLUMNS(*)))) u
JOIN duckdb_columns() c ON c.table_name = tbl AND c.column_name = u.col
  AND c.database_name = current_database() AND c.schema_name = current_schema()
GROUP BY c.column_index, u.col;
-- the select list is written from the detection in the table's own column order, with every name quoted; a table with no
-- rows detects nothing, and its statement reads it as it is rather than being NULL
CREATE TEMP MACRO recast_sql(tbl) AS (
  SELECT coalesce('SELECT ' || string_agg('TRY_CAST(' || quoted(col) || ' AS ' || detected_type || ') AS ' || quoted(col),
                                          ', ' ORDER BY column_index), 'SELECT *') || ' FROM ' || quoted(tbl)
  FROM detect_types(tbl));
-- query() takes only a constant, so the statements are kept in a variable for it to read
SET VARIABLE recast = MAP {'raw': recast_sql('raw'), 'raw2': recast_sql('raw2'), 'raw3': recast_sql('raw3')};
CREATE TEMP TABLE result AS SELECT * FROM query(getvariable('recast')['raw']);
CREATE TEMP TABLE result2 AS SELECT * FROM query(getvariable('recast')['raw2']);
CREATE TEMP TABLE result3 AS SELECT * FROM query(getvariable('recast')['raw3']);
SELECT * FROM detect_types('raw') ORDER BY column_index;
DESCRIBE result;
DESCRIBE result2;
DESCRIBE result3;
SELECT CASE WHEN ((SELECT list((column_name, column_type)) FROM (DESCRIBE result)) = [('whole', 'BIGINT'), ('day', 'DATE'), ('fraction', 'DOUBLE'), ('word', 'VARCHAR'), ('unit price', 'DOUBLE'), ('order', 'VARCHAR')] AND (SELECT sum(whole) = 23 AND max(day) = DATE '2026-02-10' AND sum(fraction) = 6.75 AND count(word) = 2 AND sum("unit price") = 14.5 AND string_agg("order", ',') = 'o-1,o-2' AND count(*) = 3 FROM result) AND (SELECT list((column_name, column_type)) FROM (DESCRIBE result2)) = [('n', 'BIGINT'), ('price', 'DOUBLE'), ('label', 'VARCHAR'), ('note', 'VARCHAR')] AND (SELECT sum(n) = 3 AND sum(price) = 3.5 FROM result2) AND (SELECT list((column_name, column_type)) FROM (DESCRIBE result3)) = [('code', 'VARCHAR'), ('qty', 'VARCHAR')] AND (SELECT count(*) = 0 FROM result3)) THEN 'as expected' ELSE error('the tables were not recast to the detected types in their own column order with their values intact, or a table without a date column, a column with no value or a table with no rows was not recast') END AS verdict;