Skip to main content

PostgreSQL Examples

This guide covers practical PostgreSQL examples — from basic CRUD operations to advanced queries and performance tricks. Each section includes a quick explanation to help you understand when and why to use these techniques.

1. Basic CRUD Operations​

CRUD stands for Create, Read, Update, and Delete — the essential operations for working with any database. Let's start with the basics:

Create a Table​

Define a new table with columns and constraints:

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT UNIQUE NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Insert Data​

Add new records to the table:

INSERT INTO users (name, email) 
VALUES ('John Doe', 'john@example.com'), ('Jane Doe', 'jane@example.com');

Select Data​

Retrieve data from the table:

SELECT * FROM users;

Update Data​

Modify existing data in the table:

UPDATE users SET email = 'newjohn@example.com' WHERE name = 'John Doe';

Delete Data​

Remove records from the table:

DELETE FROM users WHERE name = 'Jane Doe';

2. Advanced Queries​

These queries go beyond the basics — useful for analysis, conditional logic, and combining data from multiple tables.

Aggregate Functions​

Perform calculations like counting rows or finding the min/max values:

SELECT COUNT(*), MIN(created_at), MAX(created_at) FROM users;

Conditional Select with CASE​

Return different results based on conditions inside the query:

SELECT name,
  CASE 
    WHEN email LIKE '%@example.com' THEN 'Internal'
    ELSE 'External'
  END AS user_type
FROM users;

Joins (Inner, Left, Right)​

Combine data from multiple tables based on related columns:

SELECT orders.id, users.name, orders.total 
FROM orders 
INNER JOIN users ON orders.user_id = users.id;

3. Performance Tricks​

Boost query speed and database efficiency with these techniques.

Create an Index​

Indexes make lookups faster by organizing data for quick retrieval:

CREATE INDEX idx_users_email ON users(email);

Use CTEs (Common Table Expressions)​

CTEs improve readability by creating temporary result sets you can query from:

WITH user_count AS (
  SELECT COUNT(*) AS total_users FROM users
)
SELECT * FROM user_count;

Analyze Query Performance​

Get insight into how PostgreSQL runs your query to spot bottlenecks:

EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'john@example.com';

4. Security Examples​

Control who can access or modify data with PostgreSQL's security features.

Create a User with Limited Privileges​

Grant read-only access to a specific user:

CREATE USER readonly_user WITH PASSWORD 'securepassword';
GRANT SELECT ON users TO readonly_user;

Row-Level Security (RLS)​

Restrict access to rows based on the current user:

ALTER TABLE users ENABLE ROW LEVEL SECURITY;

CREATE POLICY user_policy ON users 
  FOR SELECT 
  USING (name = current_user);

5. JSON & JSONB Handling​

PostgreSQL's JSON support makes it easy to work with semi-structured data.

Create a Table with JSONB​

JSONB stores JSON data in a binary format for faster querying:

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  details JSONB
);

Insert JSON Data​

Insert data directly as JSON:

INSERT INTO products (details) VALUES ('{"name": "Laptop", "price": 1200}');

Query JSON Fields​

Extract specific fields from JSON data:

SELECT details->>'name' AS product_name FROM products;

What's Next?​

Happy querying!