Using DEFAULT for default values

Sometimes we just don't want to fill in every field by hand—maybe we're lazy or just don't have the data. In those cases, you can set up default values ahead of time so the database fills them in for you when needed.

Inserting rows into a table

Default values are super connected to inserting rows into a table. So, I'll give you a quick rundown now, and in a couple more lessons we'll come back to this topic and give it the attention it deserves.

Adding rows to a table is really simple—there's a special command for it: INSERT INTO. INSERT INTO is an SQL statement that adds new rows to a table. It's one of the most-used commands, especially when working with web forms, logs, user registration, and updating data.

Command syntax

INSERT INTO table (column1, column2, ...)
VALUES (value1, value2, ...);

Explanation:

• table — the name of the table you're adding data to.
• column1, column2, ... — a list of columns (you can skip this if you're providing values for all columns in the right order).
• value1, value2, ... — values that match up with the listed columns.

Simple example

INSERT INTO users (name, email)
VALUES ('Alice', 'alice@example.com');

This adds one row to the users table: name — "Alice", email — "alice@example.com".

Result:

Inserting multiple rows at once

You can also insert multiple records in one go—it's faster and more efficient.

INSERT INTO users (name, email)
VALUES 
  ('Alice', 'alice@example.com'),
  ('Bob', 'bob@example.net'),
  ('Charlie', 'charlie@example.org'),
  ('Diana', 'diana@example.com'),
  ('Eve', 'eve@example.net');

And here's what you'll get:

Default value

Remember the syntax for adding rows?

INSERT INTO table (column1, column2, ...)
VALUES (value1, value2, ...);

So, why do you think there's a list of columns: column1, column2? Couldn't you just provide values in the same order as the table's columns?

Well, the thing is, you can skip a lot of values when adding data. For example, PostgreSQL will figure out the ID for a new row by itself. It can also automatically fill in the current date/time for a row and put it in the right column. If you don't provide some data, PostgreSQL will just use NULL for those columns.

PostgreSQL can do a lot of handy stuff for you. And you can control this process—that's what default values are for, set up with the DEFAULT keyword.

The default value is used if you don't specify a value for a particular column when adding a record. This not only saves time, but also helps avoid random mistakes, like forgetting an important value.

How does DEFAULT work?

Let's break down the syntax. Default values are set up when you create the table. Here's the general format:

CREATE TABLE table (
    column data_type DEFAULT default_value
);

Or, if you want to add a default value to an existing table:

ALTER TABLE table
ALTER COLUMN column
SET DEFAULT default_value;

I'll talk more about changing tables in just a couple lectures :P

Example 1: Table with a DEFAULT field

Let's create a students table where every student automatically gets a registration date:

CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    registration_date DATE DEFAULT CURRENT_DATE
);

Now, when you add a new student without specifying registration_date, PostgreSQL will automatically fill in that field with the current date.

INSERT INTO students (name) VALUES ('Alex Lin');

When you run:

SELECT * FROM students;

You'll get this result:

Practical examples

Default value for a numeric column

Let's say we have a grades table, and the grade column should have a default value of 0 if the grade hasn't been set yet.

CREATE TABLE grades (
    id SERIAL PRIMARY KEY,
    student_id INTEGER NOT NULL,
    course_id INTEGER NOT NULL,
    grade INTEGER DEFAULT 0
);

Let's add a record without specifying grade:

INSERT INTO grades (student_id, course_id) VALUES (1, 101);

Now the table will look like this:

Using TEXT with a default value

Imagine a teachers table where every teacher automatically gets the status "unverified" when added.

CREATE TABLE teachers (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    status TEXT DEFAULT 'unverified'
);

Let's add a teacher without specifying the status:

INSERT INTO teachers (name) VALUES ('Peter Pall');

The result will be:

Default values for time

For a meetings table that stores meeting info, you can set a meeting_time field with a standard start time: 10:00 AM.

CREATE TABLE meetings (
    id SERIAL PRIMARY KEY,
    topic VARCHAR(100),
    meeting_time TIME DEFAULT '10:00'
);

Let's add a record:

INSERT INTO meetings (topic) VALUES ('Project discussion');

Result:

Common mistakes when using DEFAULT

Data type mismatch for the default value. If you try to set a text value for a numeric column, PostgreSQL will complain:

CREATE TABLE example (
    number INTEGER DEFAULT 'text'
);

Error: invalid input syntax for type integer.

Changing DEFAULT without considering existing records. When you change the default value, it doesn't affect records that are already in the table—they keep the old behavior. If you want to update existing rows, use UPDATE.

UPDATE students SET registration_date = '2023-01-01' WHERE registration_date IS NULL;

Mixing up DEFAULT and NOT NULL. A default value doesn't mean the field is automatically required (NOT NULL). Unless you explicitly set NOT NULL, the column can still be NULL.