Pirate Wiki

Toggle Theme

Managing Tables (DDL) ​

Data Definition Language (DDL) commands are used to define the database schema. This includes creating, modifying, and destroying tables.

CREATE TABLE ​

The CREATE TABLE statement is used to create a new table. You must define the table name, column names, and their data types.

sql
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    hire_date DATE DEFAULT CURRENT_DATE
);

Explanation of the syntax: ​

  • SERIAL: A PostgreSQL pseudo-type that creates an auto-incrementing integer.
  • PRIMARY KEY: Automatically enforces uniqueness and NOT NULL.
  • VARCHAR(50): A string with a maximum length of 50 characters.
  • NOT NULL: A constraint ensuring the column cannot be empty.
  • DEFAULT CURRENT_DATE: If no value is provided during an insert, PostgreSQL uses the current date.

ALTER TABLE ​

The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.

Adding a Column ​

sql
ALTER TABLE employees ADD COLUMN department VARCHAR(50);

Dropping a Column ​

sql
ALTER TABLE employees DROP COLUMN department;

Modifying a Column's Type ​

sql
ALTER TABLE employees ALTER COLUMN first_name TYPE VARCHAR(100);

PostgreSQL Default

When you use ALTER TABLE, PostgreSQL often requires an exclusive lock on the table. While adding a column with no default value is virtually instantaneous, rewriting a table (e.g., changing a column type that requires data casting) can block all read/write operations on large tables.

DROP TABLE ​

The DROP TABLE statement removes a table and all its data permanently.

sql
DROP TABLE employees;

To prevent an error if the table doesn't exist, use IF EXISTS:

sql
DROP TABLE IF EXISTS employees;

If other tables depend on this table (via Foreign Keys), the standard DROP TABLE will fail. You can force the drop of the table and all dependent objects using CASCADE:

sql
DROP TABLE employees CASCADE;

CAUTION

CASCADE is extremely dangerous in a production environment as it will silently drop foreign key constraints (and sometimes whole views) in other parts of your database.