Skip to content
aviral gupta
Get in touch

// B4.4 · ~30 min · Beginner

ALTER TABLE and defaults

After this lesson you can change a table that already holds data: new columns and names, new defaults and types, new rules, and ids PostgreSQL assigns itself.

Lesson 4 of 5 in B4 Changing data and schema

You will be able to

  • Add, drop and rename columns and tables, and say what the existing rows get
  • Change defaults, types and NOT NULL, knowing which rows each change checks or touches
  • Number new rows with an identity column, also on a table that already has ids

  1. Warm-up · Activity 1 of 7

    Warm-up from the last lesson. members.email is UNIQUE, and Linus already has no email. What happens when a fifth member without an email is added?

    INSERT INTO members VALUES (5, 'Alan', NULL, NULL, '2026-10-01');

  2. Predict · Activity 2 of 7

    Predict before you read on. members already has four rows. The new column is NOT NULL, so the old rows cannot stay empty. What happens?

    ALTER TABLE members
  ADD COLUMN active boolean NOT NULL DEFAULT true;

  3. Practice · Activity 3 of 7

    Fill in the key word so that the column city is called town from now on, with its values kept.

    ALTER TABLE members ____ COLUMN city TO town;
    ALTER TABLE members COLUMN city TO town;

  4. Practice · Activity 4 of 7

    Match each ALTER TABLE members … to what it does to the four existing rows.

  5. Practice · Activity 5 of 7

    An import left books.year as a text column. Which statement turns it into an integer column, so that year + 1 works again?

  6. Brain teaser · Activity 6 of 7

    Brain teaser. books got a text column signed with DEFAULT 'no', and now it should become a boolean. The USING expression is fine for every row. What happens?

    ALTER TABLE books ADD COLUMN signed text DEFAULT 'no';

ALTER TABLE books
  ALTER COLUMN signed TYPE boolean USING signed = 'yes';

  7. Apply · Activity 7 of 7

    Mini-task. In the editor of the worked example, give books a column added_on: the six books there now get 2026-09-01, books added later get 2026-10-01. Let PostgreSQL number new books after the existing ids. Then add Persuasion by Jane Austen with 1 copy, without an id or a date, and show books 6 and 7.

    Check your work against this list

Build it yourself

Read the worked example, then write the exercises. Your code runs in your browser or on your computer and is never uploaded.

Worked example

Changing the library while it holds data

setup.sql builds the library with keys and rules from the last lesson. main.sql adds a status column to members, changes its default for new members, renames city to town, and lets PostgreSQL number new loans after the existing ids. On your computer: psql -X -q -v ON_ERROR_STOP=1 -f setup.sql -f main.sql.

main.sql

-- 1. A new column: existing rows get the default.
ALTER TABLE members ADD COLUMN status text NOT NULL DEFAULT 'active';

-- 2. A new default: only for rows inserted from now on.
ALTER TABLE members ALTER COLUMN status SET DEFAULT 'new';
INSERT INTO members (id, name, joined) VALUES (5, 'Alan', '2026-10-01');

-- 3. A new name for a column.
ALTER TABLE members RENAME COLUMN city TO town;
SELECT id, name, town, status FROM members ORDER BY id;

-- 4. Number new loans automatically, after the existing ids.
ALTER TABLE loans ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY (START WITH 6);
INSERT INTO loans (book_id, member_id, loaned_on) VALUES (4, 5, '2026-10-02');
SELECT id, book_id, member_id, loaned_on FROM loans WHERE id >= 5 ORDER BY id;

setup.sql

CREATE TABLE members (
  id integer PRIMARY KEY,
  name text NOT NULL,
  email text UNIQUE,
  city text,
  joined date NOT NULL
);

CREATE TABLE books (
  id integer PRIMARY KEY,
  title text NOT NULL,
  author text,
  year integer,
  genre text,
  copies integer NOT NULL CHECK (copies >= 0)
);

INSERT INTO members VALUES
  (1, 'Ada', 'ada@example.com', 'Berlin', '2025-01-15'),
  (2, 'Grace', 'grace@example.com', 'Munich', '2025-03-02'),
  (3, 'Linus', NULL, 'Berlin', '2026-02-10'),
  (4, 'Margaret', 'margaret@example.com', NULL, '2026-05-20');

INSERT INTO books VALUES
  (1, 'Dune', 'Frank Herbert', 1965, 'sci-fi', 2),
  (2, 'Emma', 'Jane Austen', 1815, 'classic', 1),
  (3, 'Kindred', 'Octavia E. Butler', 1979, 'sci-fi', 1),
  (4, 'Beloved', 'Toni Morrison', 1987, 'fiction', 1),
  (5, 'The Hobbit', 'J. R. R. Tolkien', 1937, 'fantasy', 3),
  (6, 'Neuromancer', 'William Gibson', 1984, 'sci-fi', 1);

CREATE TABLE loans (
  id integer PRIMARY KEY,
  book_id integer NOT NULL REFERENCES books (id),
  member_id integer NOT NULL REFERENCES members (id) ON DELETE CASCADE,
  loaned_on date NOT NULL,
  returned_on date,
  CHECK (returned_on >= loaned_on)
);

INSERT INTO loans VALUES
  (1, 1, 1, '2026-09-01', '2026-09-10'),
  (2, 3, 1, '2026-09-12', NULL),
  (3, 2, 2, '2026-09-05', '2026-09-20'),
  (4, 1, 3, '2026-09-15', NULL),
  (5, 6, 2, '2026-09-18', NULL);

Run it with

psql -X -q -v ON_ERROR_STOP=1 -f setup.sql -f main.sql

Output

 id |   name   |  town  | status
----+----------+--------+--------
  1 | Ada      | Berlin | active
  2 | Grace    | Munich | active
  3 | Linus    | Berlin | active
  4 | Margaret |        | active
  5 | Alan     |        | new
(5 rows)

 id | book_id | member_id | loaned_on
----+---------+-----------+------------
  5 |       6 |         2 | 2026-09-18
  6 |       4 |         5 | 2026-10-02
(2 rows)
  • The four existing members got status active from the DEFAULT in ADD COLUMN; that is how a NOT NULL column can be added to a full table.
  • SET DEFAULT 'new' changed nothing in those rows. Only Alan, inserted afterwards without a status, got new.
  • After the rename, the values are under town. Margaret and Alan have no city, shown as empty cells.
  • The insert into loans names no id, and the identity column hands out 6, the START WITH value. Without it, the counter would start at 1 and collide with loan 1.
Change it and run it

Tab indents and Shift+Tab outdents. To leave the editor with the keyboard, press Esc, then Tab.

The first run downloads PostgreSQL for your browser (up to 10.1 MB) and keeps it cached. Every run starts from an empty database. Your code stays on your device.

Exercises

Exercise 1 of 2

A shelf for every book

Every book needs a shelf. The six books there now stand on main; books added later go to new arrivals first. Make shelf a NOT NULL column with the right default for each group, then add Persuasion (id 7, 1 copy) without a shelf and list id, title and shelf by id. The starter adds the column, but all shelves stay empty.

Tab indents and Shift+Tab outdents. To leave the editor with the keyboard, press Esc, then Tab.

The first run downloads PostgreSQL for your browser (up to 10.1 MB) and keeps it cached. Every run starts from an empty database. Your code stays on your device.

Hints
  1. Hint 1

    A DEFAULT given in ADD COLUMN fills the existing rows.

  2. Hint 2

    Then change the default with ALTER COLUMN shelf SET DEFAULT …: that touches no existing row.

  3. Hint 3

    ADD COLUMN shelf text NOT NULL DEFAULT 'main', then SET DEFAULT 'new arrivals', then the INSERT.

Show a solution

One way to solve it. Yours can look different and still pass the checks.

ALTER TABLE books ADD COLUMN shelf text NOT NULL DEFAULT 'main';
ALTER TABLE books ALTER COLUMN shelf SET DEFAULT 'new arrivals';
INSERT INTO books (id, title, copies) VALUES (7, 'Persuasion', 1);
SELECT id, title, shelf FROM books ORDER BY id;
Run it on your computer

Install PostgreSQL 18 or newer. Save these files in one folder, open a terminal in that folder, and run the commands below.

main.sql

-- Every book needs a shelf: the six old ones stand on 'main',
-- new books go to 'new arrivals' first.
ALTER TABLE books ADD COLUMN shelf text;
INSERT INTO books (id, title, copies) VALUES (7, 'Persuasion', 1);
SELECT id, title, shelf FROM books ORDER BY id;

test.sql

-- test: The six old books stand on main, Persuasion on new arrivals
-- output:
--  id |    title    |    shelf
-- ----+-------------+--------------
--   1 | Dune        | main
--   2 | Emma        | main
--   3 | Kindred     | main
--   4 | Beloved     | main
--   5 | The Hobbit  | main
--   6 | Neuromancer | main
--   7 | Persuasion  | new arrivals
-- (7 rows)

setup.sql

CREATE TABLE members (
  id integer PRIMARY KEY,
  name text NOT NULL,
  email text UNIQUE,
  city text,
  joined date NOT NULL
);

CREATE TABLE books (
  id integer PRIMARY KEY,
  title text NOT NULL,
  author text,
  year integer,
  genre text,
  copies integer NOT NULL CHECK (copies >= 0)
);

INSERT INTO members VALUES
  (1, 'Ada', 'ada@example.com', 'Berlin', '2025-01-15'),
  (2, 'Grace', 'grace@example.com', 'Munich', '2025-03-02'),
  (3, 'Linus', NULL, 'Berlin', '2026-02-10'),
  (4, 'Margaret', 'margaret@example.com', NULL, '2026-05-20');

INSERT INTO books VALUES
  (1, 'Dune', 'Frank Herbert', 1965, 'sci-fi', 2),
  (2, 'Emma', 'Jane Austen', 1815, 'classic', 1),
  (3, 'Kindred', 'Octavia E. Butler', 1979, 'sci-fi', 1),
  (4, 'Beloved', 'Toni Morrison', 1987, 'fiction', 1),
  (5, 'The Hobbit', 'J. R. R. Tolkien', 1937, 'fantasy', 3),
  (6, 'Neuromancer', 'William Gibson', 1984, 'sci-fi', 1);

CREATE TABLE loans (
  id integer PRIMARY KEY,
  book_id integer NOT NULL REFERENCES books (id),
  member_id integer NOT NULL REFERENCES members (id) ON DELETE CASCADE,
  loaned_on date NOT NULL,
  returned_on date,
  CHECK (returned_on >= loaned_on)
);

INSERT INTO loans VALUES
  (1, 1, 1, '2026-09-01', '2026-09-10'),
  (2, 3, 1, '2026-09-12', NULL),
  (3, 2, 2, '2026-09-05', '2026-09-20'),
  (4, 1, 3, '2026-09-15', NULL),
  (5, 6, 2, '2026-09-18', NULL);

psql runs the files on your own PostgreSQL server. Use an empty database you can throw away: add -d and its name to the command.

Run the program:

psql -X -q -v ON_ERROR_STOP=1 -f setup.sql -f main.sql

There is no command for the checks on your computer yet. They are in test.sql: each check query prints t when it passes.

Exercise 2 of 2

Fix the data, then add the rule

From now on every member needs an email, and the column city should be called town. Remove the -- in front of the ALTER in the starter and run it: it fails, because Linus has no email. His address is linus@example.com: store it first, then add the rule, then rename the column.

Tab indents and Shift+Tab outdents. To leave the editor with the keyboard, press Esc, then Tab.

The first run downloads PostgreSQL for your browser (up to 10.1 MB) and keeps it cached. Every run starts from an empty database. Your code stays on your device.

Hints
  1. Hint 1

    Read the error of the starter: column "email" of relation "members" contains null values. Which row is it?

  2. Hint 2

    UPDATE members SET email = 'linus@example.com' WHERE id = 3; comes first.

  3. Hint 3

    Then SET NOT NULL succeeds, and RENAME COLUMN city TO town changes the name.

Show a solution

One way to solve it. Yours can look different and still pass the checks.

UPDATE members SET email = 'linus@example.com' WHERE id = 3;
ALTER TABLE members ALTER COLUMN email SET NOT NULL;
ALTER TABLE members RENAME COLUMN city TO town;
Run it on your computer

Install PostgreSQL 18 or newer. Save these files in one folder, open a terminal in that folder, and run the commands below.

main.sql

-- Every member needs an email from now on, and city becomes town.
-- ALTER TABLE members ALTER COLUMN email SET NOT NULL;
SELECT id, name, email FROM members ORDER BY id;

test.sql

-- test: email is NOT NULL
SELECT attnotnull FROM pg_attribute WHERE attrelid = 'members'::regclass AND attname = 'email';

-- test: Linus has his email
SELECT email = 'linus@example.com' FROM members WHERE id = 3;

-- test: The column is called town and still holds Berlin for Ada
SELECT town = 'Berlin' FROM members WHERE id = 1;

-- test: There is no column city any more
SELECT NOT EXISTS (SELECT 1 FROM pg_attribute WHERE attrelid = 'members'::regclass AND attname = 'city' AND NOT attisdropped);

-- test: All four members are still there
SELECT count(*) = 4 FROM members;

setup.sql

CREATE TABLE members (
  id integer PRIMARY KEY,
  name text NOT NULL,
  email text UNIQUE,
  city text,
  joined date NOT NULL
);

CREATE TABLE books (
  id integer PRIMARY KEY,
  title text NOT NULL,
  author text,
  year integer,
  genre text,
  copies integer NOT NULL CHECK (copies >= 0)
);

INSERT INTO members VALUES
  (1, 'Ada', 'ada@example.com', 'Berlin', '2025-01-15'),
  (2, 'Grace', 'grace@example.com', 'Munich', '2025-03-02'),
  (3, 'Linus', NULL, 'Berlin', '2026-02-10'),
  (4, 'Margaret', 'margaret@example.com', NULL, '2026-05-20');

INSERT INTO books VALUES
  (1, 'Dune', 'Frank Herbert', 1965, 'sci-fi', 2),
  (2, 'Emma', 'Jane Austen', 1815, 'classic', 1),
  (3, 'Kindred', 'Octavia E. Butler', 1979, 'sci-fi', 1),
  (4, 'Beloved', 'Toni Morrison', 1987, 'fiction', 1),
  (5, 'The Hobbit', 'J. R. R. Tolkien', 1937, 'fantasy', 3),
  (6, 'Neuromancer', 'William Gibson', 1984, 'sci-fi', 1);

CREATE TABLE loans (
  id integer PRIMARY KEY,
  book_id integer NOT NULL REFERENCES books (id),
  member_id integer NOT NULL REFERENCES members (id) ON DELETE CASCADE,
  loaned_on date NOT NULL,
  returned_on date,
  CHECK (returned_on >= loaned_on)
);

INSERT INTO loans VALUES
  (1, 1, 1, '2026-09-01', '2026-09-10'),
  (2, 3, 1, '2026-09-12', NULL),
  (3, 2, 2, '2026-09-05', '2026-09-20'),
  (4, 1, 3, '2026-09-15', NULL),
  (5, 6, 2, '2026-09-18', NULL);

psql runs the files on your own PostgreSQL server. Use an empty database you can throw away: add -d and its name to the command.

Run the program:

psql -X -q -v ON_ERROR_STOP=1 -f setup.sql -f main.sql

There is no command for the checks on your computer yet. They are in test.sql: each check query prints t when it passes.

Common mistakes

A rule the existing rows do not meet

ALTER TABLE members ALTER COLUMN email SET NOT NULL;

What psql prints

ERROR:  column "email" of relation "members" contains null values

Why, and the fix

SET NOT NULL, like ADD CONSTRAINT, checks every row that is already there. Find the rows with SELECT * FROM members WHERE email IS NULL; give them a value with UPDATE (or decide the rule is wrong), then run the ALTER again.

An identity column that starts at 1 on a full table

ALTER TABLE loans ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY;
INSERT INTO loans (book_id, member_id, loaned_on) VALUES (4, 4, '2026-10-02');

What psql prints

ERROR:  duplicate key value violates unique constraint "loans_pkey"

Why, and the fix

The new counter does not look at the ids already in the table: it hands out 1, which loan 1 has. Start after the highest id: ADD GENERATED BY DEFAULT AS IDENTITY (START WITH 6), or move an existing counter with ALTER COLUMN id RESTART WITH 6.

Changing the type of a column with a default

ALTER TABLE books ADD COLUMN signed text DEFAULT 'no';
ALTER TABLE books ALTER COLUMN signed TYPE boolean USING signed = 'yes';

What psql prints

ERROR:  default for column "signed" cannot be cast automatically to type boolean

Why, and the fix

USING converts the rows, not the default. Drop the default first and set a new one afterwards: ALTER COLUMN signed DROP DEFAULT, then the TYPE change, then ALTER COLUMN signed SET DEFAULT false.

PostgreSQL in the browser: PGlite 0.5.8 (PostgreSQL 18.3), Apache-2.0 and PostgreSQL License. Licence and source

Exit ticket

5 questions, no hints. Score 80% or more to complete the lesson.

Finish every activity above to unlock the exit ticket.

Report a problem

Spotted something wrong or unclear? Say what, and it will be checked and fixed.

#

At least 20 characters.

Only if you want a reply.

Key ideas

Add, drop and rename

ALTER TABLE changes a table that already holds rows, so you need not drop it and start again. ADD COLUMN phone text adds a column that is NULL in every existing row. With a DEFAULT, the existing rows get the default instead, which is why ADD COLUMN active boolean NOT NULL DEFAULT true works on a full table. DROP COLUMN removes the column and its values; if a foreign key of another table needs it, as loans needs members.id, PostgreSQL refuses. RENAME COLUMN city TO town and RENAME TO patrons keep the data, and foreign keys follow; but the old name is gone, so a query that still says city fails.

Defaults, types and rules meet the existing rows

SET DEFAULT changes only what later INSERTs get; rows already in the table keep their values. DROP DEFAULT goes back to no default, that is NULL. TYPE changes the type of a column when every value converts on its own, such as integer to bigint, or text to varchar(5) if every value fits. Otherwise add USING with an expression that computes the new value: TYPE integer USING year::integer. SET NOT NULL and ADD CONSTRAINT check every existing row at once and fail if one breaks the rule: column "email" of relation "members" contains null values. Fix the data with UPDATE first, then add the rule.

Identity columns number rows for you

id integer GENERATED ALWAYS AS IDENTITY makes PostgreSQL fill id from a counter, 1, 2, 3 and so on, whenever an INSERT leaves it out. With ALWAYS, an explicit id is refused unless the INSERT says OVERRIDING SYSTEM VALUE; with BY DEFAULT, an explicit id wins. The column is NOT NULL, but not unique by itself, so keep the PRIMARY KEY. On a table with rows, ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY also starts at 1, and the next insert collides with loan 1. Start after the highest id instead: ADD GENERATED BY DEFAULT AS IDENTITY (START WITH 6).

Sources

Last reviewed October 4, 2026