PostgreSQL NOT NULL constraint

Education is not limited to just classrooms. It can be gained anytime, anywhere... - Ravi Ranjan (M.Tech-NIT)

PostgreSQL NOT NULL constraint

NOT NULL constraint

The not-null constraint in PostgreSQL ensures that a column can not contain any null value. This is a column constraint. No name can be defined to create a not-null constraint.

This constraint is placed immediately after the data-type of a column. Any attempt to put NULL values in that column will be rejected. Columns without the NOT NULL constraint allow NULL values.

A NOT NULL constraint is a column constraint and can not be used as a table constraint.

The reverse of NOT NULL constraint is NULL, but it is not necessary to mention NULL to create a table, the default is NULL, which does not mean that the column must contain NULL, it means that the column might contain NULL value.

A column can contain another constraint along with the NOT NULL constraint.

PostgreSQL NOT NULL example

SQL

  1. CREATE TABLE orders(  
  2. ord_no integer NOT NULL,  
  3. ord_date date NOT NULL,  
  4. item_name character(35),  
  5. item_grade character(1),  
  6. ord_qty numeric NOT NULL,  
  7. ord_amount numeric   
  8. );  

Explanation

The above example shows, the table orders have created in which the ord_no, ord_date and ord_qty column does not allow any NULL value.

PostgreSQL NULL example

SQL

  1. CREATE TABLE orders(  
  2. ord_no integer NOT NULL,  
  3. ord_date date  NULL,  
  4. item_name character(35),  
  5. item_grade character(1),  
  6. ord_qty numeric  NULL,  
  7. ord_amount numeric   
  8. );  

 

Explanation

The above example shows, the table orders have created in which the ord_no column does not allow any NULL value but the matter is NULL constraint have used as a column constraint with ord_date and ord_qty column but the structure does not show the NULL constraint, because the default value is NULL.