Changing the structure: ALTER TABLE

An application's needs change over time, and the database must keep up. ALTER TABLE is the (extremely versatile) command dedicated to modifying existing table schemas without having to drop them (risking the data inside).

Modifying Columns (ADD / DROP / ALTER)

Adding a column requires care. If the table already contains data, adding a "NOT NULL" column will throw an error in Postgres: what should it insert in the old rows? You must associate a DEFAULT.

SQL

-- Aggiungiamo una colonna
ALTER TABLE users ADD COLUMN age INTEGER; -- Ok, sarà NULL ovunque

-- Aggiungiamo NOT NULL, quindi diamogli un deafult
ALTER TABLE users ADD COLUMN active BOOLEAN NOT NULL DEFAULT true;

-- Cancellare una colonna (ATTENZIONE! i dati andranno persi)
ALTER TABLE users DROP COLUMN age;

Not just adding or removing: you often need to re-configure the type of a column or one of its constraints:

SQL

-- Cambia il tipo di dato (postgreSQL farà un 'cast' dei dati esistenti se possibile)
ALTER TABLE users ALTER COLUMN username TYPE VARCHAR(100);

-- Aggiunge il vincolo NOT NULL
ALTER TABLE users ALTER COLUMN username SET NOT NULL;

-- Rimuove il vincolo NOT NULL (ritorna ad accettare null)
ALTER TABLE users ALTER COLUMN username DROP NOT NULL;

Adding Constraints at runtime

Just like with CREATE TABLE, we can use ALTER TABLE to attach a CONSTRAINT to a table already in use in the production app.

SQL

-- Aggiungi un nuovo Foreign Key
ALTER TABLE orders
  ADD CONSTRAINT fk_order_status FOREIGN KEY (status_id) REFERENCES statuses(id);

-- Aggiungi un CHECK
ALTER TABLE products
  ADD CONSTRAINT price_positive CHECK (price > 0);

-- E per distruggerli?
ALTER TABLE products DROP CONSTRAINT price_positive;

Renaming

Both columns and tables can be renamed:

SQL

ALTER TABLE users RENAME TO auth_users;
ALTER TABLE auth_users RENAME COLUMN username TO login_name;

Try it

Exercise#sql.m9.l3.e1

Attempts: 0Loading…

The 'products' table currently has a 'name' column. Modify the table by renaming the 'name' column to 'title'. Use the RENAME COLUMN operation.

Loading editor…

Show hint

Syntax: ALTER TABLE ... RENAME COLUMN ... TO ...

Solution available after 3 attempts

Adding Constraints in post-production

Exercise#sql.m9.l3.e2

Attempts: 0Loading…

Modify the 'products' table by adding a CHECK constraint (named 'verifica_prezzo') that requires the price (column 'price') to be greater than 0. The constraint must be added without dropping the original column.

Loading editor…

Show hint

Syntax: ALTER TABLE table ADD CONSTRAINT name CHECK (...)

Solution available after 3 attempts