Default Value Constraint

On this page Carat arrow pointing down
CockroachDB v2.1 is no longer supported as of April 30, 2020. For more details, refer to the Release Support Policy.

The DEFAULT value constraint specifies a value to write into the constrained column if one is not defined in an INSERT statement. The value may be either a hard-coded literal or an expression that is evaluated at the time the row is created.


  • The data type of the Default Value must be the same as the data type of the column.
  • The DEFAULT value constraint only applies if the column does not have a value specified in the INSERT statement. You can still insert a NULL into an optional (nullable) column by explicitly inserting NULL. For example, INSERT INTO foo VALUES (1, NULL);.


You can only apply the DEFAULT value constraint to individual columns.


You can also add the DEFAULT value constraint to an existing table through ALTER COLUMN.

CREATE TABLE table_name ( column_name column_type DEFAULT default_value column_constraints , column_def table_constraints ) )
Parameter Description
table_name The name of the table you're creating.
column_name The name of the constrained column.
column_type The constrained column's data type.
default_value The value you want to insert by default, which must evaluate to the same data type as the column_type.
column_constraints Any other column-level constraints you want to apply to this column.
column_def Definitions for any other columns in the table.
table_constraints Any table-level constraints you want to apply.


> CREATE TABLE inventories (
    product_id        INT,
    warehouse_id      INT,
    quantity_on_hand  INT DEFAULT 100,
    PRIMARY KEY (product_id, warehouse_id)
> INSERT INTO inventories (product_id, warehouse_id) VALUES (1,20);
> INSERT INTO inventories (product_id, warehouse_id, quantity_on_hand) VALUES (2,30, NULL);
> SELECT * FROM inventories;
| product_id | warehouse_id | quantity_on_hand |
|          1 |           20 |              100 |
|          2 |           30 | NULL             |

If the DEFAULT value constraint is not specified and an explicit value is not given, a value of NULL is assigned to the column.

See also

Yes No
On this page

Yes No