Define a constant table in BigQuery (or Postgres)

user_4605070 0
Viewed 302

How do I define in a simple single line a temporary constant table in BigQuery (or Postgres the syntax of which will likely work in BigQuery)?

with my_fruits as (
--- ??? define a table 
--- consisting of a single column fruit_name
--- with values ('apple', 'pear', 'gooseberry', ...  (12 more fruits))
)
select * from my_fruits left join 
(select fruit_name, 1 is_present from my_dataset.your_fruits)
using (fruit_name)
google-bigquery postgresql
2 Answers

You can use UNNEST:

select * from unnest(['apple', 'pear', 'gooseberry']) as fruit_name

You also can join with UNNEST directly.

user_134735250

With Postgres you can easily create a multi-column constant table:

select * from (values (1,1,1), (1,2,3)) as x(one, two, three)

Unfortunately above doesn't work for BigQuery and not sure if there is a proper substitute for it.

user_8414290
Related