Data Modelling - Tables in PostgreSQL

Introduction

Tables are fundamental structures in PostgreSQL where data is stored. Understanding how to create, modify, and manage tables is essential for effective database management.

Creating a Table

A table in PostgreSQL is created using the CREATE TABLE statement. Here's an example of creating a table called employees:

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

CREATE TABLE
                    

This command creates a table with four columns: id, name, position, and salary.

Inserting Data into a Table

Data can be inserted into a table using the INSERT INTO statement. For example:

INSERT INTO employees (name, position, salary)
VALUES ('Alice Johnson', 'Manager', 75000);
                    

INSERT 0 1
                    

This command inserts a new row into the employees table.

Querying Data from a Table

Data can be retrieved from a table using the SELECT statement. For example:

SELECT * FROM employees;
                    

 id |     name      | position | salary 
----+---------------+----------+--------
  1 | Alice Johnson | Manager  |  75000
(1 row)
                    

This command retrieves all rows and columns from the employees table.

Updating Data in a Table

Existing data in a table can be updated using the UPDATE statement. For example:

UPDATE employees
SET salary = 80000
WHERE name = 'Alice Johnson';
                    

UPDATE 1
                    

This command updates the salary of Alice Johnson in the employees table.

Deleting Data from a Table

Rows can be deleted from a table using the DELETE FROM statement. For example:

DELETE FROM employees
WHERE name = 'Alice Johnson';
                    

DELETE 1
                    

This command deletes the row of Alice Johnson from the employees table.

Altering a Table

The structure of an existing table can be modified using the ALTER TABLE statement. For example, to add a new column:

ALTER TABLE employees
ADD COLUMN hire_date DATE;
                    

ALTER TABLE
                    

This command adds a new column called hire_date to the employees table.

Dropping a Table

A table can be removed from the database using the DROP TABLE statement. For example:

DROP TABLE employees;
                    

DROP TABLE
                    

This command deletes the employees table from the database.