I was exploring some new data, joining across multiple tables, and doing a simple SELECT * as Iβd not worked out yet which columns I actually wanted. The issue was, the same field name existing in more than one table. This meant that in the results from the query, it wasnβt clear which field came from which table:
describe select *
from t1 inner join t2 on t1.X = t2.X;
So, how to avoid ending up with ambiguous fields? In my example X is the same value because itβs the JOIN predicate, but Z could have a different value in each table but the result set has two fields called "Z".
I could alias each field by hand:
describe select t1.X as t1_X, t1.Y as t1_Y, t1.Z as t1_Z,
t2.X as t2_X, t2.Z as t2_Z
from t1 inner join t2 on t1.X = t2.X;
βββββββββββββββ¬ββββββββββββββ¬ββββββββββ¬ββββββββββ¬ββββββββββ¬ββββββββββ
β column_name β column_type β null β key β default β extra β
β varchar β varchar β varchar β varchar β varchar β varchar β
βββββββββββββββΌββββββββββββββΌββββββββββΌββββββββββΌββββββββββΌββββββββββ€
β t1_X β VARCHAR β YES β NULL β NULL β NULL β
β t1_Y β VARCHAR β YES β NULL β NULL β NULL β
β t1_Z β VARCHAR β YES β NULL β NULL β NULL β
β t2_X β VARCHAR β YES β NULL β NULL β NULL β
β t2_Z β VARCHAR β YES β NULL β NULL β NULL β
βββββββββββββββ΄ββββββββββββββ΄ββββββββββ΄ββββββββββ΄ββββββββββ΄ββββββββββ
But Iβve got a wide table so donβt want to hand-code the aliases for each field; I want to use SELECT *.
I could DESCRIBE each table, scrape the fields into a text editor, and use a search and replace or block edit to prefix the fields and build my SQL that way. But itβs manual and not reusable.
DuckDB COLUMNS to the rescue
Thanks to Alex Monahan on Bluesky (#dataBS FTW!), who pointed me at the COLUMNS expression.This nice example illustrates it in action.
Applied to my query above, it looks like this:
describe select columns(t1.*) as "t1_\0",
columns(t2.*) as "t2_\0"
from t1 inner join t2 on t1.X = t2.X;
βββββββββββββββ¬ββββββββββββββ¬ββββββββββ¬ββββββββββ¬ββββββββββ¬ββββββββββ
β column_name β column_type β null β key β default β extra β
β varchar β varchar β varchar β varchar β varchar β varchar β
βββββββββββββββΌββββββββββββββΌββββββββββΌββββββββββΌββββββββββΌββββββββββ€
β t1_X β VARCHAR β YES β NULL β NULL β NULL β
β t1_Y β VARCHAR β YES β NULL β NULL β NULL β
β t1_Z β VARCHAR β YES β NULL β NULL β NULL β
β t2_X β VARCHAR β YES β NULL β NULL β NULL β
β t2_Z β VARCHAR β YES β NULL β NULL β NULL β
βββββββββββββββ΄ββββββββββββββ΄ββββββββββ΄ββββββββββ΄ββββββββββ΄ββββββββββ
Perfect!
The t1_ is the literal prefix added to the field name, and \0 a regex identifier for the field name.
You can do more fancy renaming of fields with regex too with the COLUMNS expression; check out the docs for more examples.
You can also use the new prefix aliases (added in DuckDB 1.20) if you prefer:
describe select "t1_\0": columns(t1.*),
"t2_\0": columns(t2.*)
from t1 inner join t2 on t1.X = t2.X;
βββββββββββββββ¬ββββββββββββββ¬ββββββββββ¬ββββββββββ¬ββββββββββ¬ββββββββββ
β column_name β column_type β null β key β default β extra β
β varchar β varchar β varchar β varchar β varchar β varchar β
βββββββββββββββΌββββββββββββββΌββββββββββΌββββββββββΌββββββββββΌββββββββββ€
β t1_X β VARCHAR β YES β NULL β NULL β NULL β
β t1_Y β VARCHAR β YES β NULL β NULL β NULL β
β t1_Z β VARCHAR β YES β NULL β NULL β NULL β
β t2_X β VARCHAR β YES β NULL β NULL β NULL β
β t2_Z β VARCHAR β YES β NULL β NULL β NULL β
βββββββββββββββ΄ββββββββββββββ΄ββββββββββ΄ββββββββββ΄ββββββββββ΄ββββββββββ
Top comments (0)