Creating, Modifying, and Deleting Tables in SQL

Using PostgreSQL and pgAdmin 4

Prerequisites

Before proceeding, ensure that:

  • PostgreSQL is installed and running.
  • pgAdmin 4 is set up and connected to your PostgreSQL server.

1. Creating Tables

1.1. The CREATE TABLE Statement

The CREATE TABLE command is used to define a new table along with its columns, data types, and constraints.

General Syntax

CREATE TABLE [IF NOT EXISTS] table_name (
    column1 data_type [constraints],
    column2 data_type [constraints],
    ...
    table_constraints
);
  • IF NOT EXISTS: Prevents an error if the table already exists.
  • column1, column2, …: Names of the table columns.
  • data_type: Data type for each column (e.g., INTEGER, VARCHAR, DATE).
  • constraints: Optional rules such as PRIMARY KEY, NOT NULL, or UNIQUE.

1.2. Example: Creating a students Table

Let’s create a table named students with the following columns:

  • id: A unique identifier with an auto-incrementing integer (using SERIAL).
  • first_name: A text field.
  • last_name: A text field.
  • birthdate: A date field.
CREATE TABLE IF NOT EXISTS students (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    birthdate DATE
);

Steps in pgAdmin 4:

  • Open pgAdmin 4 and connect to your PostgreSQL server.

  • Select your database from the Object Browser.
  • Right-click the database and choose Query Tool.

  • Paste the SQL command into the Query Editor.

  • Execute the command by clicking the Execute/Refresh button (lightning bolt icon).

1.3 Verifying Table Creation

After creating a table, you can verify its creation in pgAdmin 4 by:

  • Object Explorer: Expand the “Tables” node under your schema. You should see your newly created table listed. You can further expand the table to view its columns.

2. Modifying Tables

2.1. The ALTER TABLE Statement

The ALTER TABLE command is used to modify an existing table’s structure. Common operations include adding, modifying, or dropping columns.

General Syntax

ALTER TABLE table_name
    ADD COLUMN column_name data_type [constraints];

ALTER TABLE table_name
    ALTER COLUMN column_name [SET DATA TYPE new_data_type];

ALTER TABLE table_name
    RENAME COLUMN old_column_name TO new_column_name;

ALTER TABLE table_name
    DROP COLUMN [IF EXISTS] column_name [CASCADE];

2.2. Example Operations

Adding a Column

To add an email column to the students table:

ALTER TABLE students
ADD COLUMN email VARCHAR(100);

Modifying a Column

To change the data type of the birthdate column (for example, if you decide to store timestamps instead):

ALTER TABLE students
ALTER COLUMN birthdate TYPE TIMESTAMP;

Note: When altering a column’s data type, PostgreSQL may require a USING clause to define how to convert existing data. In this case, if the conversion is straightforward, PostgreSQL can handle it automatically.

Renaming a Column

To rename the first_name column to fname:

ALTER TABLE students
RENAME COLUMN first_name TO fname;

Dropping a Column

To remove the email column from the students table:

ALTER TABLE students
DROP COLUMN IF EXISTS email;

Steps in pgAdmin 4:

  • Open the Query Tool for your target database.
  • Enter the desired ALTER TABLE command in the editor.
  • Execute the command by clicking the Execute/Refresh button.

3. Deleting Tables

3.1. The DROP TABLE Statement

The DROP TABLE command permanently deletes a table and all of its data. Use this command with caution.

General Syntax

DROP TABLE [IF EXISTS] table_name [CASCADE];
  • IF EXISTS: Prevents an error if the table does not exist.
  • CASCADE: Automatically drops objects that depend on the table (e.g., constraints, indexes).

3.2. Example: Dropping the students Table

DROP TABLE IF EXISTS students;

Steps in pgAdmin 4:

  1. Locate the table in the Object Browser under Schemas > Public > Tables.
  2. Right-click the table and select Delete/Drop.
  3. Confirm the deletion in the popup dialog.
  4. Alternatively, use the Query Tool to execute the DROP TABLE command.

Summary

In this tutorial, we covered the core SQL commands for managing table structures in PostgreSQL:

  • Creating tables using the CREATE TABLE statement.
  • Modifying tables using various forms of the ALTER TABLE command to add, modify, rename, or drop columns.
  • Deleting tables using the DROP TABLE statement.

By following the provided examples and instructions, you can effectively manage tables using PostgreSQL and pgAdmin 4. Mastery of these commands is essential for database design and maintenance.