Skip to main content

DDL Commands For Table

CREATE TABLE​

the CREATE TABLE statement is used to define a new table. It allows specifying column names, data types, constraints, and default values.

Syntax:

CREATE TABLE table_name (
    column_name data_type constraints,
    ...
);

Example:

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    age INTEGER CHECK (age > 18),
    department TEXT,
    salary NUMERIC(10,2) DEFAULT 0.00,
    hired_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
Key Features
  • Column Definitions → Each column has a name, data type, and optional constraints.
  • Constraints:
    • PRIMARY KEY → Ensures uniqueness.
    • NOT NULL → Prevents NULL values.
    • CHECK → Adds validation conditions.
    • DEFAULT → Assigns a default value.
    • FOREIGN KEY → Establishes relationships between tables.

TRUNCATE TABLE​

The TRUNCATE command is used to quickly remove all rows from a table. It is faster than the DELETE command because it does not generate individual row delete actions.

Syntax:

TRUNCATE [TABLE] table_name [, ...] [RESTART IDENTITY] [CASCADE | RESTRICT];
Parameters
  • RESTART IDENTITY → Resets the sequence associated with the table’s columns.
  • CASCADE → Automatically truncates all tables that have foreign-key references to the truncated table.
  • RESTRICT (default) → Refuses to truncate if any tables have foreign-key references to the truncated table.
TRUNCATE TABLE my_table;  // one or more tables

🔹 Removes all rows from my_table.

Example: Basic PostgreSQL TRUNCATE TABLE statement example

First, create a new table called products:

CREATE TABLE products(
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  price DECIMAL(10,2) NOT NULL DEFAULT 0
);

Second, insert some rows into the products table:

INSERT INTO products (name, price)
VALUES
  ('A', 19.99),
  ('B', 29.99),
  ('C', 39.99),
  ('D', 49.99)
RETURNING *;

Output:

 id | name | price
----+------+-------
  1 | A    | 19.99
  2 | B    | 29.99
  3 | C    | 39.99
  4 | D    | 49.99
(4 rows)

Third, delete all data from the products table using the TRUNCATE TABLE statement:

TRUNCATE TABLE products;

Output:

TRUNCATE TABLE

DROP TABLE​

The DROP TABLE statement is used to remove an existing table from the database. This operation deletes the table structure along with all the data stored in it.

Syntax:

DROP TABLE [IF EXISTS] table_name [, ...] [CASCADE | RESTRICT];
Parameters
  • IF EXISTS → Prevents an error from being thrown if the table does not exist.
  • CASCADE → Automatically drops objects that depend on the table (such as views).
  • RESTRICT (default) → Refuses to drop the table if any objects depend on it.
DROP TABLE my_table;

🔹 Removes my_table if no other objects depend on it.

Example: Basic PostgreSQL DROP TABLE statement example

First, create a new table called customers:

CREATE TABLE customers(
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  email VARCHAR(255) UNIQUE NOT NULL
);

Second, insert some rows into the customers table:

INSERT INTO customers (name, email)
VALUES
  ('Alice', 'alice@example.com'),
  ('Bob', 'bob@example.com');

Output:

 id | name  |       email
----+-------+--------------------
  1 | Alice | alice@example.com
  2 | Bob   | bob@example.com
(2 rows)

Third, drop the customers table:

DROP TABLE customers;

Output:

DROP TABLE

ALTER TABLE​

The ALTER TABLE command is used to modify an existing table’s structure.

  • Add a column
  • Drop a column
  • Change the data type of a column
  • Rename a column
  • Set a default value for a column
  • Rename a table

Syntax:

ALTER TABLE table_name action;

Add, Drop, Rename Column​

ALTER TABLE table_name ADD COLUMN new_column_name column_type;

🔹 Adds a new column to the table.

Example: Adding a new column to the employees table

First, create the employees table:

CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  department TEXT
);

Second, add a new column birthdate:

ALTER TABLE employees ADD COLUMN birthdate DATE;

Before:

 id | name | department
----+------+------------
  1 | John | Sales
  2 | Jane | HR

After:

 id | name | department | birthdate
----+------+------------+-----------
  1 | John | Sales      |
  2 | Jane | HR         |

Change Data Type​

ALTER TABLE table_name ALTER COLUMN column_name TYPE new_data_type;

🔹 Changes the data type of an existing column.

Example: Changing the data type of a column in the employees table

First, create the employees table:

CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  salary INTEGER
);

Second, change the data type of the salary column:

ALTER TABLE employees ALTER COLUMN salary TYPE NUMERIC(10, 2);

Before:

 id | name | salary
----+------+--------
  1 | John | 50000
  2 | Jane | 60000

After:

 id | name | salary
----+------+---------
  1 | John | 50000.00
  2 | Jane | 60000.00

Set Default Value​

ALTER TABLE table_name ALTER COLUMN column_name SET DEFAULT default_value;

🔹 Sets a default value for a column.

Example: Setting a default value for a column in the employees table

First, create the employees table:

CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  status VARCHAR(20)
);

Second, set a default value for the status column:

ALTER TABLE employees ALTER COLUMN status SET DEFAULT 'active';

Before:

 id | name | status
----+------+--------
  1 | John |
  2 | Jane |

After:

 id | name | status
----+------+--------
  1 | John | active
  2 | Jane | active

Rename Table​

ALTER TABLE old_table_name RENAME TO new_table_name;

🔹 Renames the table.

Example: Renaming the employees table

First, create the employees table:

CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  department TEXT
);

Second, rename the employees table to staff:

ALTER TABLE employees RENAME TO staff;

Before:

 id | name | department
----+------+------------
  1 | John | Sales
  2 | Jane | HR

After:

 id | name | department
----+------+------------
  1 | John | Sales
  2 | Jane | HR