DEFAULT values and expressions in PostgreSQL

Introduction

DEFAULT values let you set a starting value for a column when no value is given. This helps keep data complete and consistent without extra work.

When you want a column to have a common starting value automatically.

When inserting new rows but some columns can be left empty and still get a value.

When you want to use a simple calculation or function as a starting value.

When you want to avoid errors from missing data in certain columns.

When you want to save time by not typing the same value repeatedly.

Syntax

PostgreSQL

CREATE TABLE table_name (
  column_name data_type DEFAULT default_value
);

The DEFAULT value can be a constant, an expression, or a function call.

If you don't provide a value for the column during insert, the DEFAULT is used.

Examples

This sets the default name to 'Guest' if no name is given.

PostgreSQL

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name TEXT DEFAULT 'Guest'
);

This uses the current date and time as the default order date.

PostgreSQL

CREATE TABLE orders (
  order_id SERIAL PRIMARY KEY,
  order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

This sets the default price to 0.0 if no price is provided.

PostgreSQL

CREATE TABLE products (
  product_id SERIAL PRIMARY KEY,
  price NUMERIC DEFAULT 0.0
);

Sample Program

This creates an employees table with default start_date as today and default salary as 30000. We insert Alice without salary, so she gets the default. Bob has a salary given.

PostgreSQL

CREATE TABLE employees (
  employee_id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  start_date DATE DEFAULT CURRENT_DATE,
  salary NUMERIC DEFAULT 30000
);

INSERT INTO employees (name) VALUES ('Alice');
INSERT INTO employees (name, salary) VALUES ('Bob', 40000);

SELECT employee_id, name, start_date, salary FROM employees ORDER BY employee_id;

Important Notes

DEFAULT values help avoid NULLs and keep data clean.

You can use functions like CURRENT_DATE or expressions like 100 + 50 as defaults.

DEFAULT only applies when you omit the column in INSERT, not when you insert NULL explicitly.

Summary

DEFAULT sets a value automatically when none is provided.

It can be a fixed value, expression, or function.

Using DEFAULT saves time and prevents missing data.