如何在DuckDB中利用DESCRIBE结果动态筛选指定类型列

问题背景 #

假设需要从DuckDB表中筛选出特定类型的所有列（例如VARCHAR类型），先创建示例表：

CREATE TABLE dummy (x VARCHAR, y BIGINT, z VARCHAR);
INSERT INTO dummy
VALUES ('a', 0, 'a'),
  ('b', 1, 'b'),
  ('c', 2, 'c');

由于表中VARCHAR类型列数量不固定，需要实现动态查询。通过DESCRIBE语句可以获取目标列名列表：

SELECT column_name
FROM (DESCRIBE dummy)
WHERE column_type = 'VARCHAR';

但直接在COLUMNS()表达式中嵌套该子查询会触发错误：BinderException: Binder Error: Table function cannot contain subqueries，以下两种写法均会报错：

SELECT COLUMNS(
    c->c IN (
      SELECT column_name
      FROM (DESCRIBE dummy)
      WHERE column_type = 'VARCHAR'
    )
  )
FROM dummy

SELECT COLUMNS(
    c->list_contains(
      (
        SELECT column_name
        FROM (DESCRIBE dummy)
        WHERE column_type = 'VARCHAR'
      ),
      c
    )
  )
FROM dummy

可行方案 #

方案1：动态SQL（PREPARE + EXECUTE） #

DuckDB的COLUMNS()函数不支持嵌套子查询，因此可以通过动态拼接SQL语句来实现需求：

1. 先通过string_agg将目标列名拼接为逗号分隔的字符串：

SELECT string_agg(column_name, ', ') AS target_columns
FROM (DESCRIBE dummy)
WHERE column_type = 'VARCHAR';

2. 将该字符串代入查询，使用PREPARE和EXECUTE执行动态SQL：

-- 存储目标列名字符串到变量
SET target_columns = (
  SELECT string_agg(column_name, ', ')
  FROM (DESCRIBE dummy)
  WHERE column_type = 'VARCHAR'
);

-- 准备并执行动态查询
PREPARE dynamic_query AS SELECT $target_columns FROM dummy;
EXECUTE dynamic_query;

这种方式性能最优，适合大多数场景。

方案2：UNNEST + PIVOT（无动态SQL） #

如果不想使用动态SQL，可以先将表数据转为键值对格式，筛选后再转回宽表：

WITH unpivoted AS (
  SELECT 
    rowid,
    unnest(keys) AS col_name,
    unnest(values) AS col_value
  FROM dummy, lateral (SELECT keys(columns(*)), values(columns(*)))
),
filtered AS (
  SELECT u.rowid, u.col_name, u.col_value
  FROM unpivoted u
  JOIN (DESCRIBE dummy) d ON u.col_name = d.column_name
  WHERE d.column_type = 'VARCHAR'
)
SELECT * FROM filtered PIVOT (max(col_value) FOR col_name IN (SELECT DISTINCT col_name FROM filtered));

该方法无需拼接SQL，但性能略逊于动态SQL，适合小型数据集。

内容的提问来源于stack exchange，提问作者bzm3r