How to use default value of data type as column default?

Question

How can I specify the default value for a column in the DDL? I was pretty sure that the following method worked a while ago:

CREATE TABLE test
(
 col_1 CHAR(12) NOT NULL,
 col_2 INTEGER NOT NULL WITH DEFAULT,
 col_3 CHAR(12) NOT NULL WITH DEFAULT
);

I would just like to define that the DB should use the default value for the column data type (like in my example above), without specifying which value exactly.

• Lennart - Slava Ukraini: Are you sure it was for PostgreSQL? I've seen WITH DEFAULT been used in Db2, but it is not standard AFAIK.

Answer 1

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 explicit DEFAULT 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. (削除) WITH DEFAULT (削除ここまで) like you display in the question is not valid in Postgres

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: don't use the data type char(12). See:

• 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

Answer 2

You should specify what is the default. If I remember correctly the DEFAULT 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
);

• May Rest in Peace: DEFAULT can be after the NOT NULL constraint.
• Lennart - Slava Ukraini: @MayRestinPeace, yes it can in all DBMS that I know of, but I remembered vaguely that the SQL standard says that DEFAULT should be first. That's why I mentioned it
• Lennart - Slava Ukraini: I don't have the time to look up the ref now, but you can try: CREATE TABLE T ( x int default 3 not null ); vs CREATE TABLE T ( x int not null default 3 ); at developer.mimer.com/sql-2016-validator