Default values for columns in Big Query Tables

Question

Is there a way to set default values for columns in tables in big query? I would like to set 'false' as a default value for a column of boolean data type.

Solution 1

A nullable column can (trivially) have a NULL default value, but there is no other notion of default in BigQuery (you either insert a particular value or omit the value and it will have the NULL value).

That said, if you want to wrap your raw table in a View, you can map a NULL column value to any default that you like.

Solution 2

There is no default values for columns in BigQuery
The simple way would be just to use NULL (in most cases this should be acceptable from requirements prospective)
Also conside cost aspect of this - storing NULL as a default cost you zero versus storing actual value will cost you column size multipled by count of rows with default values

If NULL will work for you but you still need to have your real default value for use in application - you can use field description to store some metadata about this field including default value, so than application will be able to retrieve it and use it based on whatever application logic is

In BigQuery - View is the best way to expose your real default value

Solution 3

While you can't set a default at table level, you can do it in SQL at the time of insertion, if that works for your purposes. You can use the IF() conditional expression, as such:

SELECT IF(field IS NOT NULL, field, "default") from `table`

In fact, you can do whatever which conditional expression floats your boat.

Comments

[BJones]

You should expand a bit more on your needs.

[Lukas Eder]

Regarding the cost, if a column is NOT NULL, then the DEFAULT value doesn't have to be stored, at least not until the DEFAULT or the nullability is altered.