4

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.

  • 3
    Are you sure it was for PostgreSQL? I've seen WITH DEFAULT been used in Db2, but it is not standard AFAIK. – Lennart Jul 15 '18 at 12:32
7

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: you probably do not want to use the data type char(12) in Postgres.

| improve this answer | |
3

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
);
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.