How to use default value of data type as column default?
The default default value for any new table column is the default value of the data type.
And the default default value for data types is NULL
- which is the case for all basic data types in Postgres. But any valid value is allowed for custom types or domains.
The manual on CREATE TYPE
:
A default value can be specified, in case a user wants columns of the data type to default to something other than the null value. Specify the default with the
DEFAULT
key word. (Such a default can be overridden by an explicitDEFAULT
clause attached to a particular column.)
The manual on CREATE DOMAIN
:
The default expression will be used in any insert operation that does not specify a value for the column. If a default value is defined for a particular column, it overrides any default associated with the domain. In turn, the domain default overrides any default value associated with the underlying data type.
You only use the DEFAULT
clause for columns in CREATE TABLE
to specify a different default. Else, you do nothing. like you display in the question is not valid in PostgresWITH DEFAULT
To reset a column default to the default default of the data type (typically NULL
), drop the specified default value of the column.
The manual on ALTER TABLE
:
ALTER [ COLUMN ] column_name DROP DEFAULT
NOT NULL
constraints are related but completely independent. A column defined NOT NULL
and with no custom DEFAULT
(and no not-null default for the type) requires you to provide a not-null value for every INSERT
.
Aside: you probably do not want to use the data type char(12)
in Postgres.
- Any downsides of using data type “text” for storing strings?
- Would index lookup be noticeably faster with char vs varchar when all values are 36 chars
You should specify what is the default. If I remember correctly the default constraint should be before NOT NULL:
CREATE TABLE test
( col_1 CHAR(12) NOT NULL,
col_2 INTEGER DEFAULT 0 NOT NULL,
col_3 CHAR(12) DEFAULT '' NOT NULL
);