Creating and Altering Tables in PostgreSQL

Introduction

Tables are the fundamental building blocks of a PostgreSQL database. They store data in a structured format of rows and columns, allowing for efficient data retrieval and manipulation.

Creating Tables

To create a table in PostgreSQL, you use the CREATE TABLE statement. Here is the syntax:

CREATE TABLE table_name (
    column1 datatype constraints,
    column2 datatype constraints,
    ...
);

For example, to create a table named employees:

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    position VARCHAR(50),
    salary NUMERIC(10, 2)
);

Altering Tables

To modify an existing table, you use the ALTER TABLE statement. Common alterations include adding columns, modifying column types, and dropping columns.

Adding a Column

ALTER TABLE table_name
ADD column_name datatype;

Example:

ALTER TABLE employees
ADD date_of_hire DATE;

Modifying a Column

ALTER TABLE table_name
ALTER COLUMN column_name TYPE new_datatype;

Example:

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

Dropping a Column

ALTER TABLE table_name
DROP COLUMN column_name;

Example:

ALTER TABLE employees
DROP COLUMN position;

Best Practices

• Always define a primary key for each table.
• Use appropriate datatypes for each column to optimize storage.
• Normalize your database to reduce redundancy and improve data integrity.
• Use meaningful names for tables and columns for better readability.

FAQ