8 - Creating and Modifying Tables in PostgreSQL

Introduction to Creating Tables

In PostgreSQL, creating tables is a fundamental step in building a structured database. Tables serve as the containers for your data, defining the format and structure in which your information is stored. This guide will walk you through the process of creating and modifying tables in PostgreSQL.

Creating Tables

When creating a table in PostgreSQL, you are essentially designing the blueprint for how your data will be organized. Here’s how you can do it:


CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    hire_date DATE,
    salary DECIMAL(10, 2)
);

In this example, we have created a table called ’employees’ with columns for ’employee_id,’ ‘first_name,’ ‘last_name,’ ‘hire_date,’ and ‘salary.’ The ‘SERIAL’ data type generates unique employee IDs, and the ‘PRIMARY KEY’ ensures their uniqueness within the table.

Modifying Tables

Modifying tables is a common task in database management, as requirements often change over time. PostgreSQL provides several commands for altering the structure of a table.

Adding Columns

If you need to add a new column to an existing table, you can use the ‘ALTER TABLE’ statement:


ALTER TABLE employees
ADD COLUMN department VARCHAR(100);

Now, the ’employees’ table has an additional ‘department’ column to store department information.

Modifying Column Data Types

Sometimes, you might need to change the data type of an existing column. This can be done using the ‘ALTER TABLE’ statement with the ‘ALTER COLUMN’ clause:


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

In this example, we’ve modified the ‘salary’ column to allow for larger numeric values with a precision of 12 and a scale of 2.

Deleting Columns

If you want to remove a column from a table, use the ‘ALTER TABLE’ statement with the ‘DROP COLUMN’ clause:


ALTER TABLE employees
DROP COLUMN department;

This command removes the ‘department’ column from the ’employees’ table.

Constraints

Constraints play a crucial role in defining the rules and integrity of your data. You can add constraints to columns during table creation or modify them later.

Adding Constraints

For instance, you can add a ‘NOT NULL’ constraint to ensure a column cannot contain null values:


ALTER TABLE employees
ALTER COLUMN first_name SET NOT NULL;

With this constraint, the ‘first_name’ column must always have a value, and you cannot insert a row with a null ‘first_name’ value.

Modifying Constraints

If you need to change an existing constraint, you can use the ‘ALTER TABLE’ statement. For instance, to change the data type of a column while keeping the ‘NOT NULL’ constraint:


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

This command alters the ‘salary’ column, changing its data type and retaining the ‘NOT NULL’ constraint.

Conclusion

Creating and modifying tables in PostgreSQL are essential skills for database administrators and developers. Tables are the foundation of database organization, and knowing how to adjust their structure ensures your database remains flexible and responsive to changing requirements. With the ability to create, alter, and manage tables, you can build databases that effectively store and retrieve data while adapting to evolving needs.

8 – Creating and Modifying Tables in PostgreSQL