Default values and identity columns in BigQuery

How DEFAULT works in BigQuery tables (what you can use, when it applies, what it skips) and how identity columns generate unique integers, including the ALWAYS and BY DEFAULT modes and their limits.

A column DEFAULT fills in a value when an insert leaves the column out. An identity column is an INT64 column where BigQuery generates a unique number for you. Both apply only to new rows. An explicit NULL is not replaced by a default, and an identity column does not make anything sequential.

The examples run on temporary tables in a script. The syntax is the same on stored tables.

Column defaults

CREATE TABLE sales.orders (
  order_id INT64,
  status STRING DEFAULT 'new',
  quantity INT64 DEFAULT 1,
  note STRING
);

A column with no DEFAULT has the default NULL. The default is used when the insert omits the column, or when the statement writes the keyword DEFAULT in the column’s place. Four inserts, four behaviors:

CREATE TEMP TABLE orders (
  order_id INT64,
  status STRING DEFAULT 'new',
  quantity INT64 DEFAULT 1,
  note STRING
);

INSERT INTO orders (order_id) VALUES (1);
INSERT INTO orders (order_id, status, quantity) VALUES (2, DEFAULT, DEFAULT);
INSERT INTO orders (order_id, status, quantity) VALUES (3, NULL, NULL);
INSERT INTO orders VALUES (4, 'paid', 5, 'gift');

SELECT * FROM orders ORDER BY order_id

Row 3 is the one that catches people: NULL is a value, so the default does not apply. Write DEFAULT, or leave the column out.

What a default can be

A default is a literal, or a call to one of these functions: CURRENT_DATE, CURRENT_DATETIME, CURRENT_TIME, CURRENT_TIMESTAMP, GENERATE_UUID, RAND, SESSION_USER and ST_GEOGPOINT. You can build a STRUCT or ARRAY from them, and a struct default has to be set for the whole struct. Anything else is refused:

CREATE TEMP TABLE customers (
  customer_id INT64,
  tier STRING DEFAULT UPPER('standard')
);
SELECT 1

A default can’t use another column, and it can’t contain a subquery:

CREATE TEMP TABLE orders (
  quantity INT64,
  quantity_plus_one INT64 DEFAULT quantity + 1
);
SELECT 1

The type of the expression has to fit the column. DEFAULT 'abc' on an INT64 column fails with Column default expression has type STRING which cannot be assigned to column type INT64.

Functions run as the row is written, once per row. A GENERATE_UUID() default gives each inserted row its own value:

CREATE TEMP TABLE events (
  event_id INT64,
  token STRING DEFAULT GENERATE_UUID()
);
INSERT INTO events (event_id) VALUES (1), (2), (3);

SELECT COUNT(*) AS events, COUNT(DISTINCT token) AS distinct_tokens
FROM events

Changing a default

ALTER TABLE sales.customers ALTER COLUMN tier SET DEFAULT 'standard';
ALTER TABLE sales.customers ALTER COLUMN tier DROP DEFAULT;

A change affects future inserts only. Existing rows keep what they have:

CREATE TEMP TABLE customers (customer_id INT64, tier STRING);
INSERT INTO customers VALUES (1, 'basic');

ALTER TABLE customers ALTER COLUMN tier SET DEFAULT 'standard';
INSERT INTO customers (customer_id) VALUES (2);

SELECT customer_id, tier FROM customers ORDER BY customer_id

Adding a column with a default

ADD COLUMN ... DEFAULT is refused, and the error says how to do it in three steps:

CREATE TEMP TABLE customers (customer_id INT64);
INSERT INTO customers VALUES (1);

ALTER TABLE customers ADD COLUMN tier STRING DEFAULT 'standard'

The three steps, with the UPDATE that fills the existing rows, because the default alone leaves them NULL:

CREATE TEMP TABLE customers (customer_id INT64);
INSERT INTO customers VALUES (1);

ALTER TABLE customers ADD COLUMN tier STRING;
ALTER TABLE customers ALTER COLUMN tier SET DEFAULT 'standard';
UPDATE customers SET tier = 'standard' WHERE tier IS NULL;
INSERT INTO customers (customer_id) VALUES (2);

SELECT customer_id, tier FROM customers ORDER BY customer_id

Copies don’t keep defaults

A table made with CREATE TABLE ... AS SELECT has the same columns but no defaults:

CREATE TEMP TABLE orders (order_id INT64, status STRING DEFAULT 'new');
CREATE TEMP TABLE orders_copy AS SELECT * FROM orders;

INSERT INTO orders (order_id) VALUES (1);
INSERT INTO orders_copy (order_id) VALUES (1);

SELECT 'original' AS source, order_id, status FROM orders
UNION ALL
SELECT 'copy', order_id, status FROM orders_copy
ORDER BY source

If you need the defaults, create the table with its full definition and then insert into it.

Defaults with loads and other statements

MERGE and UPDATE can use DEFAULT too, as in UPDATE t SET b = DEFAULT WHERE TRUE. A load job applies defaults when the loaded data has fewer columns than the table, but a NULL in the file stays NULL. In INFORMATION_SCHEMA.COLUMNS the column_default column shows the expression, for example 'x' for a string default.

Identity columns

An identity column is an INT64 column that BigQuery fills with a unique number. It is the BigQuery answer to auto-increment, and Google suggests it instead of GENERATE_UUID() for surrogate keys because integers take less space and join faster.

CREATE TABLE sales.tickets (
  ticket_id INT64 GENERATED ALWAYS AS IDENTITY(START WITH 100 INCREMENT BY 10),
  title STRING
);

START WITH is the first value and INCREMENT BY the step. The parentheses are required: GENERATED ALWAYS AS IDENTITY on its own is a syntax error, and IDENTITY() takes the defaults. An increment of 0 is refused (INCREMENT BY cannot be 0), and a negative increment counts down.

CREATE TEMP TABLE tickets (
  ticket_id INT64 GENERATED ALWAYS AS IDENTITY(START WITH 100 INCREMENT BY 10),
  title STRING
);
INSERT INTO tickets (title) VALUES ('login broken'), ('slow export'), ('typo');
INSERT INTO tickets (title) VALUES ('refund');

SELECT
  COUNT(*) AS tickets,
  COUNT(DISTINCT ticket_id) AS distinct_ids,
  MIN(ticket_id) AS lowest,
  MAX(ticket_id) AS highest,
  LOGICAL_AND(MOD(ticket_id, 10) = 0) AS all_multiples_of_10
FROM tickets

The values are unique and they differ by a multiple of the increment. Google says they are not guaranteed to be in order or without gaps, and that the assignment of values to rows in one statement can vary. So never read a generated value as a row number or a count. The query above checks properties that hold on every run instead of printing the id for each title.

ALWAYS or BY DEFAULT

GENERATED ALWAYS is the mode when you don’t say. You can’t write the column at all, with a value or with NULL. Only DEFAULT is accepted:

CREATE TEMP TABLE tickets (
  ticket_id INT64 GENERATED ALWAYS AS IDENTITY(),
  title STRING
);

INSERT INTO tickets (ticket_id, title) VALUES (5, 'login broken')

An UPDATE of that column fails the same way, with Cannot explicitly write to GENERATED ALWAYS identity column 'id' in UPDATE / MERGE UPDATE statements.

GENERATED BY DEFAULT lets you supply values. NULL or DEFAULT asks for a generated one. BigQuery does not check the values you supply, so duplicates go in:

CREATE TEMP TABLE tickets (
  ticket_id INT64 GENERATED BY DEFAULT AS IDENTITY(),
  title STRING
);
INSERT INTO tickets (ticket_id, title) VALUES (7, 'login broken'), (7, 'slow export');
INSERT INTO tickets (title) VALUES ('typo');

SELECT COUNT(*) AS tickets, COUNT(DISTINCT ticket_id) AS distinct_ids
FROM tickets

Three rows, two distinct ids. An identity column is only unique if you use ALWAYS or you never write the column yourself. That also means it is not a primary key. See Primary and foreign keys.

Turn an existing column into an identity column

ALTER TABLE sales.tickets ALTER COLUMN ticket_id
  SET GENERATED BY DEFAULT AS IDENTITY(START WITH 1000 INCREMENT BY 1);

The column has to be INT64, and old rows keep their values, even NULL. Only new rows get generated numbers:

CREATE TEMP TABLE tickets (ticket_id INT64, title STRING);
INSERT INTO tickets VALUES (1, 'login broken'), (NULL, 'slow export');

ALTER TABLE tickets ALTER COLUMN ticket_id
  SET GENERATED BY DEFAULT AS IDENTITY(START WITH 1000 INCREMENT BY 1);
INSERT INTO tickets (title) VALUES ('typo');

SELECT ticket_id, title FROM tickets ORDER BY title

ALTER COLUMN ... DROP GENERATED removes the property and leaves the values. After that you can write the column again.

Limits

• One identity column per table, and it must be INT64.
• You can’t partition or cluster by it.
• You can’t append to or replace a table that has an identity column with a table copy job, and you can’t use a multi-source copy, the Storage Write API or legacy streaming on it. Batch loads, INSERT and MERGE work. A load into an ALWAYS column must leave the column out.
• Legacy SQL can read the table but not write to it.

To see the configuration, INFORMATION_SCHEMA.COLUMNS has identity_generation, identity_start and identity_increment. A BY DEFAULT column with START WITH 100 INCREMENT BY 10 reads BY DEFAULT, 100 and 10.

Which to use

For a surrogate key generated on insert, an ALWAYS identity column is unique by construction and smaller than a UUID string. For constants and timestamps, use a DEFAULT, such as DEFAULT CURRENT_TIMESTAMP() on a created-at column.