3

We unfortunately have a table with a column that has been written with the field name of "OR".

If we try to query this table - "SELECT OR FROM etc." we get an error because OR is a reserved word.

How do we query that column using BigQuery's legacy SQL queries? (We need that column and not others.)

We thought we could use "SELECT *" and BigQuery's "exclude" feature but isn't part of legacy SQL so we are unable to use it. Other ideas?

Praxiteles
  • 5,802
  • 9
  • 47
  • 78

3 Answers3

4

I ran into this issue when querying the Hacker News dataset. It appears the authors added a "by" column because they were replicating the API response keys. However, it's difficult to query and not best practice in database design.

I tried the brackets:

SELECT
   [by] as author_name
FROM
   `bigquery-public-data.hacker_news.full`
LIMIT
   1000

but received the following error:

Syntax error: Unexpected keyword BY at [2:4]

Here is a query that works on the Hacker News dataset in BigQuery with Standard SQL Dialect:

SELECT
  b.by as author_name
FROM
  `bigquery-public-data.hacker_news.full` as b
LIMIT
  1000
ethanenglish
  • 1,217
  • 2
  • 15
  • 32
2

I know that you're looking for an answer using legacy SQL (and you found one using the bracket syntax), but for future readers, or, and, etc. are valid column names for query results in standard SQL. The following query will not return an error, for instance:

WITH T AS (
  SELECT
    1 AS `or`,
    2 AS `and`
)
SELECT * FROM T;
Elliott Brossard
  • 32,095
  • 2
  • 67
  • 99
0

We found another SO answer for this - except you need to do something additional to what they describe. You cannot just add brackets to the name - you also need to give it a new column name.

The answer is to query like this: 

SELECT [OR] AS new_column_name FROM ...
Community
  • 1
  • 1
Praxiteles
  • 5,802
  • 9
  • 47
  • 78