Suppose I want to select all columns of a certain type from a DuckDB table. For example, selecting all VARCHAR
type columns, after creating a table like:
CREATE TABLE dummy (x VARCHAR, y BIGINT, z VARCHAR);
INSERT INTO dummy
VALUES ('a', 0, 'a'),
('b', 1, 'b'),
('c', 2, 'c');
Inngeneral, I might have an arbitrary number of VARCHAR
type columns, so this query should be "dynamic". I get a list of the relevant columns using DESCRIBE
:
SELECT column_name
FROM (DESCRIBE dummy)
WHERE column_type = 'VARCHAR';
This statement gives me a list of the column names which have type VARCHAR
. But how do I use this? I tried using the COLUMNS
expression:
SELECT COLUMNS(
c->c IN (
SELECT column_name
FROM (DESCRIBE dummy)
WHERE column_type = 'VARCHAR'
)
)
FROM dummy
But this gives me the error: BinderException: Binder Error: Table function cannot contain subqueries
. I don't really understand the error. I get the same error when trying:
SELECT COLUMNS(
c->list_contains(
(
SELECT column_name
FROM (DESCRIBE dummy)
WHERE column_type = 'VARCHAR'
),
c
)
)
FROM dummy
How do I connect the dots between getting a list of columns by querying DESCRIBE tbl
, and then using that list to select from tbl
?
SQL-level variables were added in DuckDB 1.1.0
e.g. SET VARIABLE
/ GETVARIABLE
duckdb.sql("""
SET VARIABLE VARCHAR_NAMES = (
SELECT LIST(column_name)
FROM (DESCRIBE dummy)
WHERE column_type = 'VARCHAR'
)
""")
duckdb.sql("""
FROM DUMMY SELECT COLUMNS(x -> x in GETVARIABLE('VARCHAR_NAMES'))
""")
┌─────────┬─────────┐
│ x │ z │
│ varchar │ varchar │
├─────────┼─────────┤
│ a │ a │
│ b │ b │
│ c │ c │
└─────────┴─────────┘