Google BigQuery: how to create a new column with SQL

my_old_table

a,b,c
1,2,3
2,3,4

CREATE TABLE IF NOT EXISTS my_dataset.my_new_table    
AS    
SELECT a,b,c,     
"my_string" AS d, current_timestamp() as timestamp     
FROM my_dataset.my_old_table   

my_new_table

a,b,c,d,timestamp
1,2,3,my_string,2020-04-22 17:09:42.987987 UTC
2,3,4,my_string,2020-04-22 17:09:42.987987 UTC

schema:
a: integer
b: integer
c: integer
d: string
timestamp: timestamp

I hope all is clear :) With this you can easily add new columns to an existing table, and also specify the data types. After that you can delete the old table, if necessary. :)


Support for ALTER TABLE ADD COLUMN was released on 2020-10-14 per BigQuery Release Notes.

So the statement as originally proposed should now work with minimal modification:

ALTER TABLE `projectID.datasetID.fooTable`
ADD COLUMN barColumn DATE;

BigQuery does not support ALTER TABLE or other DDL statements, but you could consider submitting a feature request. For now, you either need to open in the table in the BigQuery UI and then add the column with the "Add New Field" button, or if you are using the API, you can use tables.update.