PostgreSQL

Powerful Relational/Vector Database

For full documentation visit postgresql.org.

Over 10 Years | Multiple Projects | Preferred

PostgreSQL has been the database of choice of on many projects I have worked on.

PostgreSQL (also known as Postgres) is a free, open-source object-relational database management system (ORDBMS) that is widely praised for its stability, data integrity, and high performance. It uses Structured Query Language (SQL) to manage and manipulate structured data stored in tables. Beyond standard relational features, it also supports advanced non-relational capabilities, allowing you to store and query JSON documents directly.

Key Features of PostgreSQL

• ACID Compliance: Guarantees secure, reliable data transactions without data corruption.

• Concurrency Control: Uses Multi-Version Concurrency Control (MVCC), meaning read operations do not block write operations and vice-versa.

• Extensibility: Users can define custom data types, create automated triggers, and write functions using multiple programming languages like Python or C.

• NoSQL Flexibility: Native support for unstructured formats like JSONB and key-value pairs (hstore).

Step-by-Step Example

This practical example demonstrates how to build and interact with an online store tracking customers and their orders.

• 1. Create a New Database

To set up a separate environment for your project, create a new database container:

CREATE DATABASE e_commerce;

• 2. Create Tables with Relationships

Next, define two linked tables. The orders table includes a Foreign Key pointing to the customers table to ensure data integrity.

-- Create the Customers table
CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL
);

-- Create the Orders table 
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT REFERENCES customers(customer_id) ON DELETE CASCADE,
    product_name VARCHAR(100) NOT NULL,
    price NUMERIC(10, 2) NOT NULL,
    order_date DATE DEFAULT CURRENT_DATE
);

• 3. Insert Sample Data

Populate the tables with initial records. Notice how the customer_id from the customer record links directly to the transactions in the orders table.

-- Add customers
INSERT INTO customers (name, email) 
VALUES ('Alice Smith', 'alice@example.com'),
       ('Bob Jones', 'bob@example.com');

-- Add orders tied to Alice (customer_id: 1) and Bob (customer_id: 2)
INSERT INTO orders (customer_id, product_name, price) 
VALUES (1, 'Wireless Mouse', 25.99),
       (1, 'Mechanical Keyboard', 89.99),
       (2, 'Laptop Stand', 45.00);

• 4. Query and Retrieve Data

To view the data, use an INNER JOIN operation to combine related information from both tables into a single, cohesive view.

SELECT 
    customers.name, 
    orders.product_name, 
    orders.price, 
    orders.order_date
FROM orders
INNER JOIN customers ON orders.customer_id = customers.customer_id;

Query Output Result:

Example of a Function/Procedure in PostgreSQL:

-- get_playbook procedure (PostgrSQL)
DECLARE
    InputData TEXT := InputData;
    results refcursor := results;
BEGIN
    IF InputData ~ '^[0-9]+$' THEN
        OPEN results FOR 
        SELECT p.*, ps.*
        FROM playbook p 
        JOIN play_versions pv ON p.playbook_id = pv.playbook_id
        JOIN play_steps ps ON pv.version_id = ps.version_id
        WHERE p.playbook_id = InputData::INTEGER;
    ELSIF InputData ~ '^[ ]+$' THEN
        OPEN results FOR 
        SELECT p.*, ps.* 
        FROM playbook p 
        JOIN play_versions pv ON p.playbook_id = pv.playbook_id
        JOIN play_steps ps ON pv.version_id = ps.version_id
        WHERE p.description = InputData::TEXT;
    ELSE
        OPEN results FOR 
        SELECT p.*, ps.*
        FROM playbook p 
        JOIN play_versions pv ON p.playbook_id = pv.playbook_id
        JOIN play_steps ps ON pv.version_id = ps.version_id
        WHERE p.name = InputData::TEXT;
    END IF;
    EXCEPTION
       WHEN sqlstate 'P0002' THEN
          RAISE NOTICE 'play % not found', InputData;
END;

How to Get Started

To run PostgreSQL on your own computer, you can download the installer directly from the PostgreSQL Official Website. You can interact with the server through a command-line interface called psql, or manage your databases visually via desktop applications like pgAdmin.

MySQL

Powerful Relational Database 

Redis

Very Fast Relational/Vector Database 

PostgreSQL

Powerful Relational/Vector Database 

Supabase

Powerful AI Database (PostgreSQL) 

Neo4j

Modern Graph Database 

SQLite

Lightweight File Based SQL Database 

LinkedIn

My LinkedIn Account 

Telegram

My Telegram Account 

Github

My Github Account 

Discord

My Discord Account 

Facebook

My Facebook Account 

Indeed

My Indeed Account 

---
title: PostgreSQL
text: Powerful Relational/Vector Database
image: /assets/svg/postgresql_logo_icon.svg
link: 
target: 
---