SQL, how to update a table structure

By

Published · Updated

Tagged: Database

Learn how to change a SQL table structure with the ALTER TABLE command, adding a new column with ADD COLUMN or removing one with DROP COLUMN.

We can alter an existing table structure using the ALTER TABLE command, followed by the alteration you want to make:

ALTER TABLE people ADD COLUMN born_year INT;

This will add a new column with empty values:

 age |  name  | born_year 
-----+--------+-----------
  37 | Flavio |          
   8 | Roger  |          

The empty cells are NULL values.

To drop a column:

ALTER TABLE people DROP COLUMN born_year;

This will result in:

 age |  name  
-----+--------
  37 | Flavio
   8 | Roger

Dropping a column deletes its data for good. Make a backup first if you might need it.

The examples in this post use PostgreSQL. I point out where MySQL and SQLite are different.

Add a column with a default value

ALTER TABLE people ADD COLUMN country TEXT NOT NULL DEFAULT 'IT';

The existing rows get 'IT', and new rows get it when you don’t pass a value.

If you add a NOT NULL column without a default and the table already has rows, it fails, because the existing rows would have no value:

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

To add a required column with no sensible default, do it in 3 steps: add it as nullable, fill it, then make it NOT NULL:

ALTER TABLE people ADD COLUMN email TEXT;

UPDATE people SET email = lower(name) || '@example.com';

ALTER TABLE people ALTER COLUMN email SET NOT NULL;

See how to update data for more on UPDATE.

In MySQL you can choose where the new column goes with FIRST or AFTER:

ALTER TABLE people ADD COLUMN born_year INT AFTER age;

PostgreSQL and SQLite always add it as the last column.

Rename a column or a table

ALTER TABLE people RENAME COLUMN name TO first_name;

ALTER TABLE people RENAME TO persons;

The same syntax works in PostgreSQL, MySQL 8 and SQLite (3.25 or later). Renaming doesn’t touch the data. Don’t forget to update the queries in your app that use the old name.

Change the type of a column

In PostgreSQL:

ALTER TABLE people ALTER COLUMN age TYPE BIGINT;

If PostgreSQL can’t convert the old values on its own, like going from TEXT to INT, you get:

ERROR:  column "born" cannot be cast automatically to type integer
HINT:  You might need to specify "USING born::integer".

Tell it how to convert with USING:

ALTER TABLE people ALTER COLUMN born TYPE INT USING born::integer;

It fails if a value can’t be converted, like 'unknown', so clean those up first.

In MySQL use MODIFY, and repeat the full column definition:

ALTER TABLE people MODIFY age BIGINT UNSIGNED NOT NULL DEFAULT 0;

This is a common trap. MODIFY replaces the whole definition. If the column was NOT NULL with a default and you write MODIFY age BIGINT, you lose the NOT NULL and the default.

SQLite can’t change a column type. See the SQLite section below.

Change the default or NOT NULL

In PostgreSQL:

ALTER TABLE people ALTER COLUMN country SET DEFAULT 'US';
ALTER TABLE people ALTER COLUMN country DROP DEFAULT;

ALTER TABLE people ALTER COLUMN email SET NOT NULL;
ALTER TABLE people ALTER COLUMN email DROP NOT NULL;

A new default only applies to rows inserted from now on. Rows already in the table keep their value.

MySQL has ALTER COLUMN ... SET DEFAULT and DROP DEFAULT too. To change NOT NULL, use MODIFY with the full definition.

Don’t fail if the column exists

PostgreSQL has IF NOT EXISTS and IF EXISTS, handy in migration scripts that might run twice:

ALTER TABLE people ADD COLUMN IF NOT EXISTS country TEXT;
ALTER TABLE people DROP COLUMN IF EXISTS born_year;

Instead of an error you get a notice, like column "country" of relation "people" already exists, skipping. MySQL and SQLite don’t support this.

When something depends on the column

In PostgreSQL, if a view or a foreign key uses the column, DROP COLUMN refuses:

ERROR:  cannot drop column nick of table people because other objects depend on it
DETAIL:  view v depends on column nick of table people
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

DROP COLUMN nick CASCADE drops the column and the view with it. Check what depends on the column before you use it.

More than one change at once

PostgreSQL and MySQL accept several changes in one statement, separated by commas:

ALTER TABLE people
  ADD COLUMN nickname TEXT,
  ADD COLUMN level INT DEFAULT 1;

SQLite does one change per ALTER TABLE.

SQLite’s limits

SQLite supports only a few kinds of ALTER TABLE: rename a table, rename a column, add a column, drop a column (3.35 or later), and, since 3.53, ALTER COLUMN ... SET NOT NULL and DROP NOT NULL. The sqlite3 that ships with macOS is older than 3.53, so check yours with sqlite3 --version.

Adding a column has rules. These all fail:

ALTER TABLE people ADD COLUMN email TEXT NOT NULL;
-- Cannot add a NOT NULL column with default value NULL

ALTER TABLE people ADD COLUMN created_at TEXT DEFAULT CURRENT_TIMESTAMP;
-- Cannot add a column with non-constant default

ALTER TABLE people ADD COLUMN id INTEGER PRIMARY KEY;
-- Cannot add a PRIMARY KEY column

ALTER TABLE people ADD COLUMN code TEXT UNIQUE;
-- Cannot add a UNIQUE column

DROP COLUMN fails if the column is a primary key, is UNIQUE, has an index, or is used by a foreign key, a view or a trigger. Drop the index first, for example.

For anything else, like changing a type or adding a constraint, the SQLite docs tell you to rebuild the table:

PRAGMA foreign_keys = OFF;
BEGIN;

CREATE TABLE people_new (
  age INTEGER NOT NULL,
  name TEXT NOT NULL
);

INSERT INTO people_new (age, name) SELECT age, name FROM people;

DROP TABLE people;
ALTER TABLE people_new RENAME TO people;

-- recreate the indexes, triggers and views of the old table here

COMMIT;
PRAGMA foreign_keys = ON;

Create the new table and copy the data first, then rename the new one. Don’t rename the old table first: the docs warn that it can break references in triggers, views and foreign keys.

Check the result

To see the current structure of a table:

Big tables in production

In PostgreSQL most ALTER TABLE commands lock the table, so no one can read or write it until they finish. Adding a column with no default, or with a constant default like 'IT', is fast even on large tables, because PostgreSQL doesn’t rewrite the rows. Changing a column type usually rewrites the whole table, which can take a long time on a big one.

PostgreSQL also lets you run ALTER TABLE inside a transaction, so you can try a change and undo it:

BEGIN;
ALTER TABLE people DROP COLUMN born_year;
-- check that everything works
ROLLBACK;

Want me to talk about your product? You can sponsor this site.

Related posts about database: