Null Values and Default Values in PostgreSQL With Example

Null Values and Default Values

Assuring data integrity and specifying column behavior in PostgreSQL requires an understanding of NULL values and default values. NULL is frequently the implicit default value for columns, therefore even though they are different, they are closely related.

NULL Values

NULL means no value. This denotes an undefined or inapplicable value. NULL isn’t zero, empty string, or other data type. In PHP, for example, a numeric field that is blank must contain a NULL since there is no such thing as a “blank number,” although character strings can be blank and separately from NULL.

The three-valued logic system used by PostgreSQL, like normal SQL, for predicates consists of true, false, and NULL (which stands for “unknown”). This implies that NULL values are frequently the result of comparisons involving them. Both 7 = NULL and 7 <> NULL, for instance, produce NULL. NULL = NULL is false because two unknown values cannot be equal.

Use IS NULL and IS NOT NULL to clearly assert if a NULL value is present or absent. Expression = NULL is a common mistake because it doesn’t work. Applications that anticipate expression = NULL to return true if the expression is null (such as some filtered forms in Microsoft Access) can change such clauses to IS NULL by setting the transform_null_equals configuration variable to ‘on’.

Generally speaking, aggregate functions ignore NULL values. SUM(), MAX(), and MIN() only consider non-NULL numbers. If all input values are NULL, certain functions return NULL. But COUNT(*) is an exception, counting all rows regardless of column NULLs, whereas COUNT(column_name) counts only values that are not NULL in that particular column.

There are numerous functions available to handle NULL values:

COALESCE(value [, …]): Returns the arguments’ first non-NULL value. NULL is returned if all of the arguments are NULL. Nulls are often defaulted during processing or presentation. Thus, COALESCE(description, short_description, ‘(none)’) provides the description and short description if not NULL and none otherwise.

NULLIF(value1, value2): NullIF(value1, value2) is meant to handle NULL values in expressions. If value1 = value2, it returns NULL. The function returns value1 if value1 and value2 are not equal. This function is important since it reverses COALESCE. For instance, SELECT NULLIF(value, ‘(none)’) returns NULL if value is ‘(none)’ but the real content otherwise.

num_nulls(VARIADIC “any”): For counting null values in PostgreSQL parameters, use the num_nulls(VARIADIC “any”) function. VARIADIC “any” specifies its polymorphic and variadic characteristics, allowing it can accept any number of arguments of any data type. When called, num_nulls counts NULL parameters and returns an integer.

num_nonnulls(VARIADIC “any”): PostgreSQL’s num_nonnulls(VARIADIC “any”) method counts non-NULL inputs from a set. VARIADIC “any” indicates that it is a polymorphic and variadic function that can accept any number of parameters of any data type. When performed, num_nonnulls returns an integer reflecting the number of non-NULL arguments.

Unique constraints by default permit more than one NULL value in the restricted column or columns because NULLs are not regarded as being equivalent. By including the NULLS NOT DISTINCT clause in a unique constraint, this behavior can be changed. This prevents multiple NULLs and treats NULL values as duplicates. NULLS DISTINCT is another way to explicitly define the default behavior.

By performing a right-click and choosing Edit -> Edit cell -> Set to NULL, users can set a cell’s value to NULL in DBeaver. Null values can be treated differently when pasting data with options like Insert NULLs and NULL value mark. Additionally, a column’s percentage of NULLs can be specified during mock data production.

Code Example:

CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name TEXT,
    marks INT
);
INSERT INTO students (name, marks) VALUES
('Arun', 80),
('Bala', NULL);
SELECT name, marks FROM students WHERE marks IS NULL;
SELECT name, marks FROM students WHERE marks IS NOT NULL;
SELECT id, COALESCE(name, 'No Name') AS student_name,
       COALESCE(marks, 0) AS safe_marks
FROM students;

Output:

CREATE TABLE
INSERT 0 2
 name | marks 
------+-------
 Bala |      
(1 row)
 name | marks 
------+-------
 Arun |    80
(1 row)
 id | student_name | safe_marks 
----+--------------+------------
  1 | Arun         |         80
  2 | Bala         |          0
(2 rows)

Default Values

A column in PostgreSQL receives a default value when a new entry is generated without a value. The implicit default for a column without a declaration is NULL. Default values can be fixed literal values like 9.99 or dynamic values created by functions or expressions like CURRENT_TIMESTAMP for a timestamp column to record row insertion time or nextval() for serial numbers. SERIAL and BIGSERIAL create an integer column with a sequence object for default values.

To set a column’s default value, omit it from the INSERT statement’s column list, use the DEFAULT keyword in the VALUES list, or use the INSERT INTO table_name DEFAULT VALUES; statement to set all columns to their default values Modify table items Change column price to 7.77 or drop default in ALTER TABLE goods.

To prevent NULLs in columns with always-expected values, data integrity requires default values. Adding a column with a fixed default value in PostgreSQL 11 no longer changes every row, making ALTER TABLE fast on large tables. Next time the row is requested, the default value is returned and applied when the table is overwritten. Right-click the cell and select “Edit -> Set to default,” where the default value, if any, will be displayed in parentheses.

Page Content

Tutorials

Null Values and Default Values in PostgreSQL With Example