Thread: [GENERAL] Default column value

   Reading the 9.6 docs suggests an answer to my question, but does not
explicitly answer it, so I ask here.

   If a column has a default value specified does this mean the column cannot
contain a NULL value? In other words, is DEFAULT <some_value> NOT NULL
redundant?

TIA,

Rich

On 12/30/2016 06:38 AM, Rich Shepard wrote:
>   Reading the 9.6 docs suggests an answer to my question, but does not
> explicitly answer it, so I ask here.
>
>   If a column has a default value specified does this mean the column
> cannot
> contain a NULL value? In other words, is DEFAULT <some_value> NOT NULL
> redundant?

No:

test=> create table default_test(id int, fld_1 varchar DEFAULT NULL);
CREATE TABLE

test=> \d default_test
       Table "public.default_test"
  Column |       Type        | Modifiers
--------+-------------------+-----------
  id     | integer           |
  fld_1  | character varying |

>
> TIA,
>
> Rich
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

Rich Shepard <rshepard@appl-ecosys.com> writes:
>    If a column has a default value specified does this mean the column cannot
> contain a NULL value? In other words, is DEFAULT <some_value> NOT NULL
> redundant?

No, because you can explicitly insert a null.  DEFAULT only controls
what happens when you omit the column in an INSERT command.

            regards, tom lane

On 12/30/2016 06:38 AM, Rich Shepard wrote:
>   Reading the 9.6 docs suggests an answer to my question, but does not
> explicitly answer it, so I ask here.
>
>   If a column has a default value specified does this mean the column
> cannot
> contain a NULL value? In other words, is DEFAULT <some_value> NOT NULL
> redundant?

Another way of looking at it:

test=> create table default_test_2(id int, fld_1 varchar DEFAULT 'test');
CREATE TABLE

test=> insert into default_test_2 values (1);
INSERT 0 1

test=> insert into default_test_2 values (2, NULL);
INSERT 0 1

test=> \pset
null                     'NULL'

test=> select * from default_test_2 ;
 id | fld_1
----+-------
  1 | test
  2 | NULL
(2 rows)


DEFAULT is what is the column is set to if the user does not specify a value.
As shown above a user can supply a NULL value. To guard against that the NOT NULL
constraint is required.

>
> TIA,
>
> Rich
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

On Fri, 30 Dec 2016, Tom Lane wrote:

> No, because you can explicitly insert a null. DEFAULT only controls what
> happens when you omit the column in an INSERT command.

tom,

   Thanks for clarifying. I did not pick this up from reading the manual and
knew that NULL could be an explicitly-defined default value.

Much appreciated,

Rich

On Fri, 30 Dec 2016, Adrian Klaver wrote:

> DEFAULT is what is the column is set to if the user does not specify a
> value. As shown above a user can supply a NULL value. To guard against
> that the NOT NULL constraint is required.

   Thanks, Adrian. This was not clear to me when I read the manual.

Happy New Year,

Rich

On Fri, 30 Dec 2016, Adrian Klaver wrote:

> DEFAULT is what is the column is set to if the user does not specify a
> value. As shown above a user can supply a NULL value. To guard against
> that the NOT NULL constraint is required.

   One more case I'd appreciate being clarified: when the column's value has
a check constraint with acceptable values in a list. For example,

param_units VARCHAR(8) DEFAULT 'mg/L'
     CONSTRAINT param_units
       CHECK (param_units IN ('ppm', 'mg/L', 'ug/L', 'umho/cm', 'percent', 'cfm', 'gpm')),

   Seems to me that if values are constrained by a list a NULL cannot be
entered by the user. Is this correct?

Rich

On Fri, 30 Dec 2016, David G. Johnston wrote:

> "The CHECK clause specifies an expression producing a Boolean result which
> new or updated rows must satisfy for an insert or update operation to
> succeed. Expressions evaluating to TRUE or UNKNOWN succeed."
>
> NULL == "UNKNOWN"

David,

   I forgot about that. Thanks for pointing it out to me.

Regards,

Rich

On 12/30/2016 06:46 AM, Adrian Klaver wrote:
> On 12/30/2016 06:38 AM, Rich Shepard wrote:

> test=> \d default_test
>       Table "public.default_test"
>  Column |       Type        | Modifiers
> --------+-------------------+-----------
>  id     | integer           |
>  fld_1  | character varying |
>
>>

To further illustrate this, NULL means UNKNOWN, not DEFAULT. Using
Adrian's example:

postgres=# create table default_test(id int, fld_1 varchar DEFAULT NULL);
CREATE TABLE
postgres=# INSERT into default_test VALUES(1,NULL);
INSERT 0 1
postgres=# INSERT into default_test VALUES(1,DEFAULT);
INSERT 0 1
postgres=# select * from default_test ;
  id | fld_1
----+-------
   1 |
   1 |
(2 rows)

postgres=# alter table default_test alter column fld_1 set default now();
ALTER TABLE
postgres=# INSERT into default_test VALUES(1,DEFAULT);
INSERT 0 1
postgres=# INSERT into default_test VALUES(1,NULL);
INSERT 0 1
postgres=# select * from default_test ;
  id |             fld_1
----+-------------------------------
   1 |
   1 |
   1 | 2016-12-30 09:11:11.170948-08
   1 |
(4 rows)

Sincerely,

JD

--
Command Prompt, Inc.                  http://the.postgres.company/
                         +1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.

On 12/30/2016 08:51 AM, Rich Shepard wrote:
> On Fri, 30 Dec 2016, David G. Johnston wrote:
>
>> "The CHECK clause specifies an expression producing a Boolean result
>> which
>> new or updated rows must satisfy for an insert or update operation to
>> succeed. Expressions evaluating to TRUE or UNKNOWN succeed."
>>
>> NULL == "UNKNOWN"
>
> David,
>
>   I forgot about that. Thanks for pointing it out to me.

What it comes down to is if you do not want NULL values in a column then
specify NOT NULL on the column.

>
> Regards,
>
> Rich
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com