SQL CREATE, ALTER, DROP TABLE

CREATE, ALTER and DROP TABLE

CREATE TABLE makes a new table, ALTER TABLE changes its columns, and DROP TABLE removes it together with all its rows.

Syntax

How it works: these statements change a table's structure, not its rows. To check a structure, query information_schema.columns, which lists every column of every table (Oracle uses ALL_TAB_COLUMNS instead).

Code
CREATE TABLE table_name (
  column1 TYPE [NOT NULL] [DEFAULT value],
  column2 TYPE,
  ...
);
CREATE TABLE new_table AS SELECT ...;          -- a new table from a query
 
ALTER TABLE table_name ADD COLUMN column TYPE;
ALTER TABLE table_name RENAME COLUMN old_name TO new_name;
ALTER TABLE table_name ALTER COLUMN column TYPE new_type;
ALTER TABLE table_name DROP COLUMN column;
ALTER TABLE table_name RENAME TO new_name;
ALTER TABLE table_name ADD CONSTRAINT constraint_name rule;
 
DROP TABLE [IF EXISTS] table_name;

Type	Holds
`INTEGER`	whole numbers
`NUMERIC(p, s)`	exact decimals: p digits in all, s of them after the point, like `NUMERIC(10, 2)` for money
`VARCHAR(n)`	text of up to n characters
`TEXT`	text of any length
`BOOLEAN`	true or false
`DATE` / `TIMESTAMPTZ`	a day / a moment in time (`TIMESTAMPTZ` is PostgreSQL's name)

CREATE TABLE is much the same everywhere, but ALTER TABLE differs:

Change	PostgreSQL	MySQL	SQL Server	Oracle
new type	`ALTER COLUMN c TYPE t`	`MODIFY COLUMN c t`	`ALTER COLUMN c t`	`MODIFY (c t)`
rename a column	`RENAME COLUMN`	`RENAME COLUMN`	`sp_rename`	`RENAME COLUMN`
rename a table	`RENAME TO`	`RENAME TO`	`sp_rename`	`RENAME TO`
table from a query	`CREATE TABLE … AS SELECT`	`CREATE TABLE … AS SELECT`	`SELECT … INTO new_table`	`CREATE TABLE … AS SELECT`

Examples

Create a table

Make a table, choosing a type for each column and which values are required.

Code
CREATE TABLE notes (
  note_id INTEGER,
  title VARCHAR(80) NOT NULL,
  body TEXT,
  pinned BOOLEAN NOT NULL DEFAULT false,
  created_on DATE
);

Inspect a table

See a table's columns, types and rules without reading its rows.

Code
SELECT column_name, data_type, character_maximum_length, is_nullable, column_default
FROM information_schema.columns
WHERE table_name = 'categories'
ORDER BY ordinal_position;

Change a table

Add, rename, retype or remove a column of a table that already has rows.

Code
ALTER TABLE customers ADD COLUMN nickname VARCHAR(30);             -- NULL in every row
ALTER TABLE customers RENAME COLUMN nickname TO display_name;
ALTER TABLE customers ALTER COLUMN display_name TYPE VARCHAR(60);
ALTER TABLE customers DROP COLUMN display_name;
 
-- Fails: some emails are longer than 10 characters
ALTER TABLE customers ALTER COLUMN email TYPE VARCHAR(10);

Add a rule to a table

ADD CONSTRAINT gives an existing table a new rule, with a name you choose. From then on, every change has to follow it.

Code
ALTER TABLE products ADD CONSTRAINT chk_stock_not_negative CHECK (stock >= 0);
-- Fails: the new rule refuses a negative stock
UPDATE products SET stock = -1 WHERE product_id = 3;

Copy into a new table

Save a query's result as a table of its own, then name it the way you want.

Code
CREATE TABLE out_of_stock AS
SELECT product_id, product_name, price
FROM products
WHERE stock = 0;
 
ALTER TABLE out_of_stock RENAME TO sold_out;

Remove a table

Delete a table you no longer need, rows and all.

Code
DROP TABLE accounts;                  -- the accounts table and all its rows
DROP TABLE IF EXISTS gift_cards;      -- no error, although there is no such table
 
-- Fails: other tables point to customers
DROP TABLE customers;

Remember

A type change fails if the existing values can't be converted.
CREATE TABLE ... AS copies columns and rows, but not keys; PostgreSQL also leaves out defaults and NOT NULL rules.
DROP TABLE deletes the rows too, and a table that others point to can't be dropped.
A table name can't be reused until the old table is renamed or dropped.
Check your work in information_schema.columns.

Practice

CREATE, ALTER and DROP TABLE

CREATE TABLE makes a new table, ALTER TABLE changes its columns, and DROP TABLE removes it together with all its rows.

Syntax

Code
CREATE TABLE table_name (
  column1 TYPE [NOT NULL] [DEFAULT value],
  column2 TYPE,
  ...
);
CREATE TABLE new_table AS SELECT ...;          -- a new table from a query
 
ALTER TABLE table_name ADD COLUMN column TYPE;
ALTER TABLE table_name RENAME COLUMN old_name TO new_name;
ALTER TABLE table_name ALTER COLUMN column TYPE new_type;
ALTER TABLE table_name DROP COLUMN column;
ALTER TABLE table_name RENAME TO new_name;
ALTER TABLE table_name ADD CONSTRAINT constraint_name rule;
 
DROP TABLE [IF EXISTS] table_name;

Type	Holds
`INTEGER`	whole numbers
`NUMERIC(p, s)`	exact decimals: p digits in all, s of them after the point, like `NUMERIC(10, 2)` for money
`VARCHAR(n)`	text of up to n characters
`TEXT`	text of any length
`BOOLEAN`	true or false
`DATE` / `TIMESTAMPTZ`	a day / a moment in time (`TIMESTAMPTZ` is PostgreSQL's name)

CREATE TABLE is much the same everywhere, but ALTER TABLE differs:

Change	PostgreSQL	MySQL	SQL Server	Oracle
new type	`ALTER COLUMN c TYPE t`	`MODIFY COLUMN c t`	`ALTER COLUMN c t`	`MODIFY (c t)`
rename a column	`RENAME COLUMN`	`RENAME COLUMN`	`sp_rename`	`RENAME COLUMN`
rename a table	`RENAME TO`	`RENAME TO`	`sp_rename`	`RENAME TO`
table from a query	`CREATE TABLE … AS SELECT`	`CREATE TABLE … AS SELECT`	`SELECT … INTO new_table`	`CREATE TABLE … AS SELECT`

Examples

Create a table

Make a table, choosing a type for each column and which values are required.

Code
CREATE TABLE notes (
  note_id INTEGER,
  title VARCHAR(80) NOT NULL,
  body TEXT,
  pinned BOOLEAN NOT NULL DEFAULT false,
  created_on DATE
);

Inspect a table

See a table's columns, types and rules without reading its rows.

Code
SELECT column_name, data_type, character_maximum_length, is_nullable, column_default
FROM information_schema.columns
WHERE table_name = 'categories'
ORDER BY ordinal_position;

Change a table

Add, rename, retype or remove a column of a table that already has rows.

Code
ALTER TABLE customers ADD COLUMN nickname VARCHAR(30);             -- NULL in every row
ALTER TABLE customers RENAME COLUMN nickname TO display_name;
ALTER TABLE customers ALTER COLUMN display_name TYPE VARCHAR(60);
ALTER TABLE customers DROP COLUMN display_name;
 
-- Fails: some emails are longer than 10 characters
ALTER TABLE customers ALTER COLUMN email TYPE VARCHAR(10);

Add a rule to a table

ADD CONSTRAINT gives an existing table a new rule, with a name you choose. From then on, every change has to follow it.

Code
ALTER TABLE products ADD CONSTRAINT chk_stock_not_negative CHECK (stock >= 0);
-- Fails: the new rule refuses a negative stock
UPDATE products SET stock = -1 WHERE product_id = 3;

Copy into a new table

Save a query's result as a table of its own, then name it the way you want.

Code
CREATE TABLE out_of_stock AS
SELECT product_id, product_name, price
FROM products
WHERE stock = 0;
 
ALTER TABLE out_of_stock RENAME TO sold_out;

Remove a table

Delete a table you no longer need, rows and all.

Code
DROP TABLE accounts;                  -- the accounts table and all its rows
DROP TABLE IF EXISTS gift_cards;      -- no error, although there is no such table
 
-- Fails: other tables point to customers
DROP TABLE customers;

Remember

A type change fails if the existing values can't be converted.
CREATE TABLE ... AS copies columns and rows, but not keys; PostgreSQL also leaves out defaults and NOT NULL rules.
DROP TABLE deletes the rows too, and a table that others point to can't be dropped.
A table name can't be reused until the old table is renamed or dropped.
Check your work in information_schema.columns.

SQLBuilding Your Own Tables

Syntax

Examples

Create a table

Inspect a table

Change a table

Add a rule to a table

Copy into a new table

Remove a table

Remember

Practice

SQLBuilding Your Own Tables

Syntax

Examples

Create a table

Inspect a table

Change a table

Add a rule to a table

Copy into a new table

Remove a table

Remember

Practice