I'm trying to use PyPika to build a select statement. Both tables have an id column, and I'm trying to select all columns from both tables except the id column from the first table.
This is the query I want to end up with, which runs properly in BigQuery:
SELECT t.* EXCEPT(id), s.* from t join s on t.id = s.id
I haven't been able to figure out how to get the EXCEPT into the select clause.
I've tried the following syntax, but the except_of() isn't allowed in this context:
t = Table("t")
s = Table("s")
query = Query.from_(t).join(s).on(t.id == s.id).select(t.star.except_of("id")).select(s.star)
I also tried a custom function, but that puts an invalid comma in the resulting query. This is what I tried:
except_select = CustomFunction("EXCEPT", ["column_to_exclude"])
query = Query.from_(t).join(s).on(t.id == s.id).select(t.star).except_select("id")).select(s.star)
which incorrectly generates
SELECT t.*, EXCEPT(id), s.* from t join s on t.id = s.id
Does anyone have any suggestions on how to solve this problem?