NOT NULL

The constraint NOT NULL prevents NULL values from appearing in a column. Figure  shows the creation of a table with a NOT NULL constraint.  

        test=> CREATE TABLE not_null_test (
        test(>                             col1 INTEGER, 
        test(>                             col2 INTEGER NOT NULL
        test(>                            );
        CREATE
        test=> INSERT INTO not_null_test
        test-> VALUES (1, NULL);
        ERROR:  ExecAppend: Fail to add null value in not null attribute col2
        test=> INSERT INTO not_null_test (col1)
        test-> VALUES (1);
        ERROR:  ExecAppend: Fail to add null value in not null attribute col2
        test=> INSERT INTO not_null_test VALUES (1, 1);
        INSERT 174368 1
        test=> UPDATE  not_null_test SET col2 = NULL;
        ERROR:  ExecReplace: Fail to add null value in not null attribute col2  

Insertion of a NULL value, or an INSERT that would set col2 to NULL, causes the INSERT to fail. As shown in the figure, an UPDATE of a NULL value also fails.

Figure  adds a DEFAULT value for col2.   

        test=> CREATE TABLE not_null_with_default_test (
        test(>                                          col1 INTEGER,
        test(>                                          col2 INTEGER NOT NULL DEFAULT 5
        test(>                                         );
        CREATE
        test=> INSERT INTO not_null_with_default_test (col1) 
        test-> VALUES (1);
        INSERT 148520 1
        test=> SELECT * 
        test-> FROM not_null_with_default_test;
         col1 | col2 
        ------+------
            1 |    5
        (1 row)  

This addition permits INSERTs that do not specify a value for col2.