Home
PostgreSQL

PostgreSQL Tables

Mastering table creation in PostgreSQL is fundamental to database management. We've covered the basics, including syntax, constraints, foreign key...

PostgreSQL, renowned for its reliability and extensibility, empowers users with robust tools for data management. One of its fundamental features is table creation, a pivotal step in structuring a database.

PostgreSQL Tables

In this article, we'll delve into the intricacies of creating tables in PostgreSQL, accompanied by illustrative examples.

Introduction to PostgreSQL Tables

Tables are the backbone of relational databases, serving as containers for organized data. PostgreSQL follows SQL standards in table creation, allowing users to define columns, specify data types, and set constraints.

Basic PostgreSQL Table Creation Syntax

Let's start with a simple example. Consider a scenario where we want to create a table to store information about employees.

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

This command initiates the creation of a table named "employees" with various columns such as , , , , , and . Let's break down this example:

  • SERIAL : This is a shorthand for an auto-incrementing integer. It's commonly used for primary key columns.
  • PRIMARY KEY : Indicates that the column is the primary key for the table, ensuring each record has a unique identifier.
  • VARCHAR(50) : Represents a variable character string with a maximum length of 50 characters, suitable for storing names.
  • DATE : Designates the column to store date values.
  • INT : Denotes an integer type for the column.
  • DECIMAL(10, 2) : Specifies a decimal data type for the column, capable of holding numbers with up to 10 digits, 2 of which can be after the decimal point.

Adding Constraints and Defaults in PostgreSQL table 

PostgreSQL allows users to impose constraints on columns, ensuring data integrity. Let's enhance our "employees" table with constraints and default values.

CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    birthdate DATE,
    department_id INT,
    salary DECIMAL(10, 2) DEFAULT 0.00,
    CONSTRAINT positive_salary CHECK (salary >= 0)
);

  • NOT NULL: Specifies that and must have values, ensuring no null entries. 
  • DEFAULT 0.00: Assigns a default value of 0.00 to the column, applicable when no value is provided during insertion. 
  • CONSTRAINT positive_salary CHECK (salary >= 0): Implements a check constraint, ensuring that the salary is non-negative.

PostgreSQL Table Creation with Foreign Keys

In a relational database, establishing relationships between tables is crucial. Let's create a new table for departments and link it to the "employees" table using a foreign key.

CREATE TABLE departments (
    department_id SERIAL PRIMARY KEY,
    department_name VARCHAR(50) NOT NULL
);
-- Adding a foreign key in the "employees" table
ALTER TABLE employees
ADD CONSTRAINT fk_department
FOREIGN KEY (department_id)
REFERENCES departments(department_id);

This example introduces a new table, "departments," and establishes a foreign key relationship with the "employees" table. The foreign key in the "employees" table references the primary key  in the "departments" table.

Composite Primary Keys and Unique Constraints

In certain scenarios, a combination of columns may be required to form a unique identifier. Let's create a table for projects where the combination of  and forms a composite primary key.

CREATE TABLE projects (
    project_id SERIAL,
    department_id INT,
    project_name VARCHAR(50) NOT NULL,
    PRIMARY KEY (project_id, department_id),
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

Here, the syntax denotes a composite primary key, ensuring the combination of and is unique.

Inheritance in PostgreSQL Tables

PostgreSQL supports table inheritance, allowing a new table to inherit columns and constraints from an existing table. Let's create a table for temporary employees that inherits from the "employees" table.

CREATE TABLE temporary_employees (
    contract_duration INT,
    hourly_rate DECIMAL(8, 2),
    PRIMARY KEY (employee_id),
    FOREIGN KEY (employee_id) REFERENCES employees(employee_id)
) INHERITS (employees);

In this example, the "temporary_employees" table inherits the structure of the "employees" table. It includes additional columns like and while maintaining a foreign key relationship with the original "employees" table.

Conclusion

Mastering table creation in PostgreSQL is fundamental to database management. We've covered the basics, including syntax, constraints, foreign keys, composite primary keys, and table inheritance. As you embark on your PostgreSQL journey, understanding these concepts will empower you to design well-structured and efficient databases. Remember to adapt these principles to your specific use cases, ensuring that your tables align with the unique requirements of your data model. With these tools in hand, you're well on your way to harnessing the full potential of PostgreSQL for effective data organization and retrieval.