13

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.

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

2 Answers 2

13

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:

7

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
);
3
  • DEFAULT can be after the NOT NULL constraint. Sep 4, 2021 at 10:38
  • 1
    @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 Sep 4, 2021 at 11:46
  • 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 Sep 4, 2021 at 11:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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