Learn SQL: Day 2 – SELECT Queries (The Foundation of SQL)

Today we start writing queries.

Today’s Goals

By the end of Day 2 you should understand:

SELECT
WHERE
ORDER BY
LIMIT
OFFSET
DISTINCT
IN
BETWEEN
LIKE
ILIKE
NULL handling
ActiveRecord equivalents
Common interview questions
Common mistakes

Step 1: Create Our Practice Database

Connect:

psql sql_day1

Let’s create a new table.

			
DROP TABLE IF EXISTS users;
CREATE TABLE users (
  id BIGSERIAL PRIMARY KEY,
  name VARCHAR(100),
  email VARCHAR(255),
  age INTEGER,
  city VARCHAR(100),
  salary NUMERIC(10,2),
  active BOOLEAN,
  created_at TIMESTAMP DEFAULT NOW()
);

		

Insert Sample Data

			
INSERT INTO users
(name, email, age, city, salary, active)
VALUES
('John',  'john@test.com', 30, 'New York', 70000, true),
('Mary',  'mary@test.com', 25, 'Chicago', 60000, true),
('Bob',   'bob@test.com', 35, 'Chicago', 90000, false),
('Alice', 'alice@test.com', 28, 'Boston', 75000, true),
('Tom',   'tom@test.com', 40, 'New York', 120000, false),
('Sara',  'sara@test.com', 32, 'Boston', 85000, true),
('Mike',  'mike@test.com', NULL, 'Chicago', NULL, true);

		

View data:

SELECT * FROM users;

1. SELECT

The most basic query.

SELECT * FROM users;

Meaning:

Give me all columns from users

Output:

id | name | email | age | city | salary

Select Specific Columns

Instead of everything:

SELECT name, email FROM users;

Output:

			
name   | email
--------+---------------
John   | john@test.com
Mary   | mary@test.com

Rails Equivalent

User.select(:name, :email)

Senior Insight

Avoid:

SELECT *

in production systems unless needed.

Why?

Because fetching unnecessary columns:

uses more memory
transfers more data
slows queries

Good:

SELECT id, name FROM users;

2. WHERE Clause

Filters rows.

Example

Only active users.

			
SELECT * FROM users
WHERE active = true;

Rails:

User.where(active: true)

Age Greater Than 30

			
SELECT * FROM users
WHERE age > 30;

Rails:

User.where("age > ?", 30)

Multiple Conditions

			
SELECT * FROM users
WHERE city = 'Chicago'
AND active = true;

Rails:

User.where(city: "Chicago", active: true)

OR

			
SELECT * FROM users
WHERE city = 'Chicago'
OR city = 'Boston';

Rails:

User.where(city: ["Chicago", "Boston"])

Interview Question

Which runs first?

WHERE A OR B AND C

Answer:

AND

before

OR

Use parentheses.

			
WHERE (A OR B)
AND C

3. ORDER BY

Sort results.

Ascending

			
SELECT * FROM users
ORDER BY age ASC;

Smallest age first.

Descending

			
SELECT * FROM users
ORDER BY salary DESC;

Highest salary first.

Rails:

User.order(salary: :desc)

Multiple Columns

			
SELECT * FROM users
ORDER BY city ASC, salary DESC;

Meaning:

			
Sort by city first
Inside each city
sort by salary

Common Interview Question

What happens if you omit ASC/DESC?

ORDER BY age

Default:

ASC

4. LIMIT

Return only N rows.

			
SELECT * FROM users
LIMIT 3;

Rails:

User.limit(3)

Why LIMIT Matters

Imagine:

10 million rows

Fetching all:

			
slow
memory-heavy
unnecessary

LIMIT reduces work.

5. OFFSET

Skip rows.

			
SELECT * FROM users
LIMIT 3
OFFSET 3;

Meaning:

			
Skip first 3
Return next 3

Rails

User.limit(3).offset(3)

Pagination Example

Page 1

LIMIT 10 OFFSET 0

Page 2

LIMIT 10 OFFSET 10

Page 3

LIMIT 10 OFFSET 20

Senior Insight

Large OFFSET values become expensive.

Example:

OFFSET 500000

PostgreSQL still scans through those rows.

Later we’ll learn:

Keyset Pagination

which is much faster.

6. DISTINCT

Remove duplicates.

Example:

SELECT city FROM users;

Result:

			
Chicago
Chicago
Chicago
Boston
Boston
New York

		

Distinct:

SELECT DISTINCT city FROM users;

Result:

			
Chicago
Boston
New York

Rails

User.select(:city).distinct

Multiple Columns

SELECT DISTINCT city, active FROM users;

Distinct applies to the combination.

7. IN

Cleaner alternative to multiple OR conditions.

Instead of:

			
WHERE city='Boston'
OR city='Chicago'
OR city='New York'

Use:

			
WHERE city IN
('Boston','Chicago','New York');

Rails:

User.where(city: ["Boston", "Chicago", "New York"])

8. BETWEEN

Range filtering.

Age between 25 and 35.

			
SELECT * FROM users
WHERE age BETWEEN 25 AND 35;

Equivalent:

			
age >= 25
AND
age <= 35

Rails

User.where(age: 25..35)

Salary Range

			
SELECT * FROM users
WHERE salary BETWEEN 60000 AND 90000;

Interview Question

Is BETWEEN inclusive?

Answer:

YES

Both boundaries included.

9. LIKE

Pattern matching.

Find names beginning with M.

			
SELECT * FROM users
WHERE name LIKE 'M%';

Result:

			
Mary
Mike

Ends With

WHERE email LIKE '%test.com'

Contains

WHERE name LIKE '%ar%'

Matches:

			
Mary
Sara

Wildcards

Symbol	Meaning
%	Any number of chars
_	Exactly one char

Example

WHERE name LIKE '_o%'

Matches:

			
Bob
Tom

10. ILIKE

PostgreSQL-specific.

Case-insensitive LIKE.

			
SELECT * FROM users
WHERE name ILIKE 'john';

Matches:

			
John
JOHN
john
JoHn

Rails

User.where("name ILIKE ?", "john")

Senior Interview Insight

In PostgreSQL:

LIKE

is case-sensitive.

ILIKE

is case-insensitive.

Many developers don’t know this.

11. NULL Handling

This is a favorite interview topic.

Let’s inspect:

SELECT * FROM users;

Mike has:

			
age = NULL
salary = NULL

Wrong

WHERE age = NULL

Returns:

Nothing

Correct

WHERE age IS NULL

Find users with no salary:

			
SELECT * FROM users
WHERE salary IS NULL;

Rails

User.where(age: nil)

Generates:

IS NULL

NOT NULL

			
SELECT * FROM users
WHERE salary IS NOT NULL;

Why NULL Is Special

SQL uses:

			
TRUE
FALSE
UNKNOWN

not just:

			
TRUE
FALSE

This is called:

Three-Valued Logic

Interviewers love asking this.

Practical Exercises

Exercise 1

Find all active users.

Exercise 2

Find users older than 30.

Exercise 3

Find users from Boston.

Exercise 4

Find top 3 highest-paid users.

Exercise 5

Find unique cities.

Exercise 6

Find users aged between 25 and 35.

Exercise 7

Find names starting with S.

Exercise 8

Find users whose salary is NULL.

Combining Everything

Example:

			
SELECT name, city, salary
FROM users
WHERE active = true
  AND city IN ('Chicago', 'Boston')
  AND salary IS NOT NULL
ORDER BY salary DESC
LIMIT 3;

		

Can you explain what this query does before running it?

That’s exactly the kind of reasoning expected in senior interviews.

ActiveRecord Translation Challenge

Convert this SQL:

			
SELECT *
FROM users
WHERE city = 'Chicago'
AND active = true
ORDER BY salary DESC
LIMIT 2;

		

into ActiveRecord.

Common Mistakes

Mistake 1

WHERE age = NULL

Wrong.

Use:

WHERE age IS NULL

Mistake 2

Using:

SELECT *

everywhere.

Mistake 3

Forgetting ORDER BY when using LIMIT.

LIMIT 5

without ordering can return arbitrary rows.

Mistake 4

Using huge OFFSET values.

Senior-Level Knowledge

Understand that SQL logically executes in this order:

			
FROM
WHERE
SELECT
DISTINCT
ORDER BY
LIMIT

		

Even though we write:

			
SELECT ...
FROM ...
WHERE ...

PostgreSQL conceptually processes the clauses in the above order.

This understanding becomes extremely important when we move to:

JOINs
GROUP BY
HAVING
Query Optimization
EXPLAIN ANALYZE

Homework

Create a new table:

			
CREATE TABLE products (
  id BIGSERIAL PRIMARY KEY,
  name VARCHAR(100),
  category VARCHAR(100),
  price NUMERIC(10,2),
  stock_quantity INTEGER
);

		

Insert at least 10 records.

Practice:

SELECT specific columns
WHERE with multiple conditions
ORDER BY price DESC
LIMIT 5
DISTINCT categories
BETWEEN on price
LIKE searches
Products with stock_quantity IS NULL

Day 3 Preview

Next we’ll cover one of the most important interview topics:

JOINs

Including:

INNER JOIN
LEFT JOIN
RIGHT JOIN
FULL JOIN
CROSS JOIN
Self Join
ActiveRecord joins
includes vs joins vs preload vs eager_load
Real Rails interview questions

Day 3 is where SQL starts becoming truly powerful.

Happy Learning! 🚀

Learn SQL: Day 1 – Relational Database Fundamentals

We’re going to learn these topics at three levels simultaneously:

Database Level (PostgreSQL)
SQL Level
Rails / ActiveRecord Level

For every topic, ask yourself:

“How does PostgreSQL store this?”

“How do I query this with SQL?”

“How does Rails represent this?”

This will help you to prepare for interviews. We use PostgreSQL here.

Today’s Goal

By the end of Day 1, you should fully understand:

Database
Table
Row
Column
Primary Key
Foreign Key
Constraints
One-to-One
One-to-Many
Many-to-Many
Rails associations behind them

Part 1: What is a Database?

Imagine you are building an e-commerce application.

You need to store:

Users
Products
Orders
Payments

A database is simply a structured place to store and retrieve that information.

In PostgreSQL:

CREATE DATABASE shop_app;

Connect to it:

psql postgres

Inside psql:

CREATE DATABASE shop_app;

Connect:

\c shop_app

Verify:

SELECT current_database();

Output:

 shop_app

Part 2: What is a Table?

A table is similar to an Excel sheet.

Example:

Users table

id	name	email
1	John	john@test.com
2	Mary	mary@test.com

Create it:

			
CREATE TABLE users (
  id BIGSERIAL PRIMARY KEY,
  name VARCHAR(100),
  email VARCHAR(255)
);

		

Verify:

\d users

Interview Question:

Why not store everything in a single giant table?

Answer:

Because:

duplication increases
maintenance becomes difficult
relationships become unclear
updates become expensive

This concept is called normalization (we’ll study later).

Part 3: Rows

A row represents one record.

Insert data:

			
INSERT INTO users (name, email)
VALUES
('John', 'john@test.com'),
('Mary', 'mary@test.com');

View:

SELECT * FROM users;

Output:

 id | name | email
----+------+----------------
 1  | John | john@test.com
 2  | Mary | mary@test.com

Each row = one user.

Part 4: Columns

Columns describe attributes.

In users table:

			
id
name
email

View columns:

\d users

Senior Insight:

A database table models an entity.

Examples:

Entity	Table
User	users
Product	products
Order	orders

Columns represent attributes of that entity.

Part 5: Primary Keys

Every row needs a unique identifier.

Example:

id BIGSERIAL PRIMARY KEY

Meaning:

No duplicates.

No NULLs.

Try:

			
INSERT INTO users (id, name)
VALUES (1, 'Bob');

You should get:

duplicate key value violates unique constraint

Why Primary Keys Exist

Without a primary key:

			
John
John
John

Which John?

Nobody knows.

Primary key solves identity.

Rails Equivalent

Migration:

			
create_table :users do |t|
  t.string :name
  t.string :email
end

Rails automatically adds:

id

as the primary key.

Part 6: Constraints

Constraint = database rule.

Interviewers love this topic.

NOT NULL

Create:

			
CREATE TABLE products (
  id BIGSERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL
);

Try:

			
INSERT INTO products(name)
VALUES(NULL);

Fails.

UNIQUE

			
CREATE TABLE customers (
  id BIGSERIAL PRIMARY KEY,
  email VARCHAR(255) UNIQUE
);

Duplicate email:

			
INSERT INTO customers(email)
VALUES('test@test.com');
INSERT INTO customers(email)
VALUES('test@test.com');

Fails.

CHECK Constraint

Age must be positive.

			
CREATE TABLE employees (
  id BIGSERIAL PRIMARY KEY,
  age INTEGER CHECK(age > 0)
);

Fails:

			
INSERT INTO employees(age)
VALUES(-5);

Why Constraints Matter

Junior developer:

validates :email, uniqueness: true

Senior developer:

			
validates :email, uniqueness: true
+
UNIQUE(email)

Because application validations can be bypassed.

Database constraints cannot.

Part 7: Foreign Keys

Now let’s model:

User has many orders.

Create users:

			
CREATE TABLE users (
  id BIGSERIAL PRIMARY KEY,
  name VARCHAR(100)
);

Create orders:

			
CREATE TABLE orders (
  id BIGSERIAL PRIMARY KEY,
  user_id BIGINT,
  total NUMERIC(10,2)
);

		

Foreign key:

			
ALTER TABLE orders
ADD CONSTRAINT fk_orders_user
FOREIGN KEY (user_id)
REFERENCES users(id);

Insert user:

			
INSERT INTO users(name)
VALUES('John');

Insert order:

			
INSERT INTO orders(user_id,total)
VALUES(1,100);

Works.

Try:

			
INSERT INTO orders(user_id,total)
VALUES(999,100);

Fails.

Because user doesn’t exist.

Why Foreign Keys Exist

Without them:

Order belongs to user 999

But user 999 doesn’t exist.

Database becomes corrupted.

Rails Equivalent

			
class User < ApplicationRecord
  has_many :orders
end
class Order < ApplicationRecord
  belongs_to :user
end

		

Migration:

			
t.references :user,
             null: false,
             foreign_key: true

Rails creates:

			
user_id
FOREIGN KEY

behind the scenes.

Part 8: One-to-Many Relationship

Most common relationship.

Example:

User -> Orders

One user:

John

Many orders:

			
Order 1
Order 2
Order 3

Diagram:

			
users
-----
id
orders
------
id
user_id

		

Rails:

			
User has_many :orders
Order belongs_to :user

Practical Exercise

Insert:

			
INSERT INTO users(name)
VALUES('Mary');

Orders:

			
INSERT INTO orders(user_id,total)
VALUES
(2,50),
(2,75),
(2,120);

		

Query:

			
SELECT *
FROM orders
WHERE user_id = 2;

Part 9: One-to-One Relationship

Less common.

Example:

			
User
Profile

Each user has exactly one profile.

Create profile table:

			
CREATE TABLE profiles (
  id BIGSERIAL PRIMARY KEY,
  user_id BIGINT UNIQUE,
  bio TEXT,
  FOREIGN KEY(user_id)
  REFERENCES users(id)
);

		

Notice:

UNIQUE(user_id)

This forces:

			
One user
One profile

Rails:

			
class User < ApplicationRecord
  has_one :profile
end
class Profile < ApplicationRecord
  belongs_to :user
end

		

Interview Question:

How does a database enforce one-to-one?

Answer:

			
FOREIGN KEY
+
UNIQUE

on the foreign key column.

Part 10: Many-to-Many Relationship

Classic interview topic.

Example:

			
Students
Courses

Student can enroll in many courses.

Course can have many students.

Create students:

			
CREATE TABLE students (
  id BIGSERIAL PRIMARY KEY,
  name VARCHAR(100)
);

Create courses:

			
CREATE TABLE courses (
  id BIGSERIAL PRIMARY KEY,
  title VARCHAR(100)
);

Need a join table:

			
CREATE TABLE enrollments (
  student_id BIGINT,
  course_id BIGINT,
  PRIMARY KEY(student_id, course_id),
  FOREIGN KEY(student_id)
    REFERENCES students(id),
  FOREIGN KEY(course_id)
    REFERENCES courses(id)
);

		

Diagram:

			
students
    |
    |
enrollments
    |
    |
courses

		

Rails

			
class Student < ApplicationRecord
  has_many :enrollments
  has_many :courses, through: :enrollments
end
class Course < ApplicationRecord
  has_many :enrollments
  has_many :students, through: :enrollments
end
class Enrollment < ApplicationRecord
  belongs_to :student
  belongs_to :course
end

		

Senior-Level Insight

Most Rails developers stop at:

			
has_many
belongs_to

Strong backend engineers understand:

			
Association
      ↓
Foreign Key
      ↓
Constraint
      ↓
Index
      ↓
Storage

		

That understanding helps you:

debug production issues
optimize queries
design schemas
answer interview questions confidently

Interview Questions

Try answering without looking.

Q1

Difference between:

PRIMARY KEY

and

UNIQUE

Q2

Can a table have multiple UNIQUE constraints?

Q3

Can a table have multiple PRIMARY KEYS?

Q4

How is a one-to-one relationship implemented in PostgreSQL?

Q5

Why should foreign keys exist even when Rails validations exist?

Q6

What problem does a join table solve?

Practical Lab (Run Everything)

Create a fresh database:

CREATE DATABASE interview_sql_day1;

Connect:

\c interview_sql_day1

Create:

			
users
profiles
orders
students
courses
enrollments

		

Insert sample data.

Then practice:

			
SELECT * FROM users;
SELECT * FROM orders;
SELECT * FROM profiles;
SELECT * FROM enrollments;

Try intentionally violating:

PRIMARY KEY
UNIQUE
NOT NULL
FOREIGN KEY

and observe PostgreSQL’s error messages.

A senior engineer learns a lot from database errors.

Homework

Exercise 1

Create:

			
authors
books

One author → many books

Add proper foreign keys.

Exercise 2

Create:

			
employees
employee_details

One-to-one relationship.

Exercise 3

Create:

			
movies
actors
movie_actors

Many-to-many relationship.

Insert:

3 movies
5 actors

Create relationships.

Exercise 4

For every relationship above, write the equivalent Rails models and associations.

Day 2 Preview

Next we’ll cover the foundation of everything in SQL:

SELECT Queries

Including:

SELECT
WHERE
ORDER BY
LIMIT
OFFSET
DISTINCT
IN
BETWEEN
LIKE
ILIKE
NULL handling

plus PostgreSQL execution behavior and ActiveRecord equivalents.

This is where real querying begins.

Happy Learning! 🚀

🧬 Extracting and Joining on Ancestry Values in PostgreSQL: A Complete Guide

I am working on a project where we face issues in an ancestral path data in PostgreSql DB. Working with hierarchical data in PostgreSQL often involves dealing with ancestry paths stored as delimited strings. This comprehensive guide explores how to extract specific values from ancestry columns and utilize them effectively in join operations, complete with practical examples, troubleshooting tips and how I fixed the issues.

🎯 Introduction

PostgreSQL’s robust string manipulation capabilities make it ideal for handling complex hierarchical data structures. When working with ancestry values stored in text columns, you often need to extract specific parts of the hierarchy for data analysis, reporting, or joining operations.

This article demonstrates how to:

✨ Extract values from ancestry strings using regular expressions
🔗 Perform efficient joins on extracted ancestry data
🛡️ Handle edge cases and avoid common pitfalls
⚡ Optimize queries for better performance

❓ Problem Statement

📊 Scenario

Consider a projects table with an ancestry column containing hierarchical paths like:

-- Sample ancestry values
"6/4/5/3"     -- Parent chain: 6 → 4 → 5 → 3
"1/2"         -- Parent chain: 1 → 2
"9"           -- Single parent: 9
NULL          -- Root level project

🎯 Goal

We need to:

Extract the last integer value from the ancestry path
Use this value in a JOIN operation to fetch parent project data
Handle edge cases like NULL values and malformed strings

🏗️ Understanding the Data Structure

📁 Table Structure

CREATE TABLE projects (
    id BIGINT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    ancestry TEXT,  -- Stores parent hierarchy as "id1/id2/id3"
    created_at TIMESTAMP DEFAULT NOW()
);

-- Sample data
INSERT INTO projects (id, name, ancestry) VALUES
    (1, 'Root Project', NULL),
    (2, 'Department A', '1'),
    (3, 'Team Alpha', '1/2'),
    (4, 'Task 1', '1/2/3'),
    (5, 'Subtask 1A', '1/2/3/4');

🧭 Ancestry Path Breakdown

Project ID	Name	Ancestry	Immediate Parent
1	Root Project	`NULL`	None (root)
2	Department A	`1`	1
3	Team Alpha	`1/2`	2
4	Task 1	`1/2/3`	3
5	Subtask 1A	`1/2/3/4`	4

🔧 Solution Overview

🎯 Core Approach

🔍 Pattern Matching: Use regex to identify the last number in the ancestry string
✂️ Value Extraction: Extract the matched value using regexp_replace()
🔄 Type Conversion: Cast the extracted string to the appropriate numeric type
🔗 Join Operation: Use the converted value in JOIN conditions

📝 Basic Query Structure

SELECT projects.*
FROM projects
LEFT OUTER JOIN projects AS parent_project 
    ON CAST(
        regexp_replace(projects.ancestry, '.*\/(\d+)$', '\1')
        AS BIGINT
    ) = parent_project.id
WHERE projects.ancestry IS NOT NULL;

📝 Regular Expression Deep Dive

🎯 **Pattern Breakdown: `.*\/(\d+)$`**

Let’s dissect this regex pattern:

.*      -- Match any characters (greedy)
\/      -- Match literal forward slash
(\d+)   -- Capture group: one or more digits
$       -- End of string anchor

📊 Pattern Matching Examples

Ancestry String	Regex Match	Captured Group	Result
`"6/4/5/3"`	`5/3`	`3`	✅ 3
`"1/2"`	`1/2`	`2`	✅ 2
`"9"`	No match	–	❌ Original string
`"abc/def"`	No match	–	❌ Original string

🔧 Alternative Regex Patterns

-- For single-level ancestry (no slashes)
regexp_replace(ancestry, '^(\d+)$', '\1')

-- For extracting first parent instead of last
regexp_replace(ancestry, '^(\d+)\/.*', '\1')

-- For handling mixed delimiters (/ or -)
regexp_replace(ancestry, '.*[\/\-](\d+)$', '\1')

💻 Implementation Examples

🔧 Example 1: Basic Parent Lookup

-- Find each project with its immediate parent information
SELECT 
    p.id,
    p.name AS project_name,
    p.ancestry,
    parent.id AS parent_id,
    parent.name AS parent_name
FROM projects p
LEFT OUTER JOIN projects parent 
    ON CAST(
        regexp_replace(p.ancestry, '.*\/(\d+)$', '\1')
        AS BIGINT
    ) = parent.id
WHERE p.ancestry IS NOT NULL
ORDER BY p.id;

Expected Output:

 id | project_name | ancestry | parent_id | parent_name
----+--------------+----------+-----------+-------------
  2 | Department A | 1        |         1 | Root Project
  3 | Team Alpha   | 1/2      |         2 | Department A
  4 | Task 1       | 1/2/3    |         3 | Team Alpha
  5 | Subtask 1A   | 1/2/3/4  |         4 | Task 1

🎯 Example 2: Handling Edge Cases

-- Robust query that handles all edge cases
SELECT 
    p.id,
    p.name AS project_name,
    p.ancestry,
    CASE 
        WHEN p.ancestry IS NULL THEN 'Root Level'
        WHEN p.ancestry !~ '.*\/(\d+)$' THEN 'Single Parent'
        ELSE 'Multi-level'
    END AS hierarchy_type,
    parent.name AS parent_name
FROM projects p
LEFT OUTER JOIN projects parent ON 
    CASE 
        -- Handle multi-level ancestry
        WHEN p.ancestry ~ '.*\/(\d+)$' THEN
            CAST(regexp_replace(p.ancestry, '.*\/(\d+)$', '\1') AS BIGINT)
        -- Handle single-level ancestry
        WHEN p.ancestry ~ '^\d+$' THEN
            CAST(p.ancestry AS BIGINT)
        ELSE NULL
    END = parent.id
ORDER BY p.id;

📈 Example 3: Aggregating Child Counts

-- Count children for each project
WITH parent_child_mapping AS (
    SELECT 
        p.id AS child_id,
        CASE 
            WHEN p.ancestry ~ '.*\/(\d+)$' THEN
                CAST(regexp_replace(p.ancestry, '.*\/(\d+)$', '\1') AS BIGINT)
            WHEN p.ancestry ~ '^\d+$' THEN
                CAST(p.ancestry AS BIGINT)
            ELSE NULL
        END AS parent_id
    FROM projects p
    WHERE p.ancestry IS NOT NULL
)
SELECT 
    p.id,
    p.name,
    COUNT(pcm.child_id) AS direct_children_count
FROM projects p
LEFT JOIN parent_child_mapping pcm ON p.id = pcm.parent_id
GROUP BY p.id, p.name
ORDER BY direct_children_count DESC;

🚨 Common Errors and Solutions

❌ Error 1: “invalid input syntax for type bigint”

Problem:

-- ❌ Incorrect: Casting entire ancestry string
CAST(projects.ancestry AS BIGINT) = parent.id

Solution:

-- ✅ Correct: Cast only the extracted value
CAST(
    regexp_replace(projects.ancestry, '.*\/(\d+)$', '\1') 
    AS BIGINT
) = parent.id

❌ Error 2: Unexpected Results with Single-Level Ancestry

Problem: Single values like "9" don’t match the pattern .*\/(\d+)$

Solution:

-- ✅ Handle both multi-level and single-level ancestry
CASE 
    WHEN ancestry ~ '.*\/(\d+)$' THEN
        CAST(regexp_replace(ancestry, '.*\/(\d+)$', '\1') AS BIGINT)
    WHEN ancestry ~ '^\d+$' THEN
        CAST(ancestry AS BIGINT)
    ELSE NULL
END

❌ Error 3: NULL Ancestry Values Causing Issues

Problem: NULL values can cause unexpected behaviour in joins

Solution:

-- ✅ Explicitly handle NULL values
WHERE ancestry IS NOT NULL 
AND ancestry != ''

🛡️ Complete Error-Resistant Query

SELECT 
    p.id,
    p.name AS project_name,
    p.ancestry,
    parent.id AS parent_id,
    parent.name AS parent_name
FROM projects p
LEFT OUTER JOIN projects parent ON 
    CASE 
        WHEN p.ancestry IS NULL OR p.ancestry = '' THEN NULL
        WHEN p.ancestry ~ '.*\/(\d+)$' THEN
            CAST(regexp_replace(p.ancestry, '.*\/(\d+)$', '\1') AS BIGINT)
        WHEN p.ancestry ~ '^\d+$' THEN
            CAST(p.ancestry AS BIGINT)
        ELSE NULL
    END = parent.id
ORDER BY p.id;

⚡ Performance Considerations

📊 Indexing Strategies

-- Create index on ancestry for faster pattern matching
CREATE INDEX idx_projects_ancestry ON projects (ancestry);

-- Create partial index for non-null ancestry values
CREATE INDEX idx_projects_ancestry_not_null 
ON projects (ancestry) 
WHERE ancestry IS NOT NULL;

-- Create functional index for extracted parent IDs
CREATE INDEX idx_projects_parent_id ON projects (
    CASE 
        WHEN ancestry ~ '.*\/(\d+)$' THEN
            CAST(regexp_replace(ancestry, '.*\/(\d+)$', '\1') AS BIGINT)
        WHEN ancestry ~ '^\d+$' THEN
            CAST(ancestry AS BIGINT)
        ELSE NULL
    END
) WHERE ancestry IS NOT NULL;

🔄 Query Optimization Tips

🎯 Use CTEs for Complex Logic

WITH parent_lookup AS (
    SELECT 
        id,
        CASE 
            WHEN ancestry ~ '.*\/(\d+)$' THEN
                CAST(regexp_replace(ancestry, '.*\/(\d+)$', '\1') AS BIGINT)
            WHEN ancestry ~ '^\d+$' THEN
                CAST(ancestry AS BIGINT)
        END AS parent_id
    FROM projects
    WHERE ancestry IS NOT NULL
)
SELECT p.*, parent.name AS parent_name
FROM parent_lookup p
JOIN projects parent ON p.parent_id = parent.id;

⚡ Consider Materialized Views for Frequent Queries

CREATE MATERIALIZED VIEW project_hierarchy AS
SELECT 
    p.id,
    p.name,
    p.ancestry,
    CASE 
        WHEN p.ancestry ~ '.*\/(\d+)$' THEN
            CAST(regexp_replace(p.ancestry, '.*\/(\d+)$', '\1') AS BIGINT)
        WHEN p.ancestry ~ '^\d+$' THEN
            CAST(p.ancestry AS BIGINT)
    END AS parent_id
FROM projects p;

-- Refresh when data changes
REFRESH MATERIALIZED VIEW project_hierarchy;

🛠️ Advanced Techniques

🔍 Extracting Multiple Ancestry Levels

-- Extract all ancestry levels as an array
SELECT 
    id,
    name,
    ancestry,
    string_to_array(ancestry, '/') AS ancestry_array,
    -- Get specific levels
    split_part(ancestry, '/', 1) AS level_1,
    split_part(ancestry, '/', 2) AS level_2,
    split_part(ancestry, '/', -1) AS last_level
FROM projects
WHERE ancestry IS NOT NULL;

🧮 Calculating Hierarchy Depth

-- Calculate the depth of each project in the hierarchy
SELECT 
    id,
    name,
    ancestry,
    CASE 
        WHEN ancestry IS NULL THEN 0
        ELSE array_length(string_to_array(ancestry, '/'), 1)
    END AS hierarchy_depth
FROM projects
ORDER BY hierarchy_depth, id;

🌳 Building Complete Hierarchy Paths

-- Recursive CTE to build full hierarchy paths
WITH RECURSIVE hierarchy_path AS (
    -- Base case: root projects
    SELECT 
        id,
        name,
        ancestry,
        name AS full_path,
        0 AS level
    FROM projects 
    WHERE ancestry IS NULL

    UNION ALL

    -- Recursive case: child projects
    SELECT 
        p.id,
        p.name,
        p.ancestry,
        hp.full_path || ' → ' || p.name AS full_path,
        hp.level + 1 AS level
    FROM projects p
    JOIN hierarchy_path hp ON 
        CASE 
            WHEN p.ancestry ~ '.*\/(\d+)$' THEN
                CAST(regexp_replace(p.ancestry, '.*\/(\d+)$', '\1') AS BIGINT)
            WHEN p.ancestry ~ '^\d+$' THEN
                CAST(p.ancestry AS BIGINT)
        END = hp.id
)
SELECT * FROM hierarchy_path
ORDER BY level, id;

✅ Best Practices

🎯 Data Validation

✅ Validate Ancestry Format on Insert/Update

-- Add constraint to ensure valid ancestry format
ALTER TABLE projects 
ADD CONSTRAINT check_ancestry_format 
CHECK (
    ancestry IS NULL 
    OR ancestry ~ '^(\d+)(\/\d+)*$'
);

🔍 Regular Data Integrity Checks

-- Find orphaned projects (ancestry points to non-existent parent)
SELECT p.id, p.name, p.ancestry
FROM projects p
WHERE p.ancestry IS NOT NULL
AND NOT EXISTS (
    SELECT 1 FROM projects parent
    WHERE parent.id = CASE 
        WHEN p.ancestry ~ '.*\/(\d+)$' THEN
            CAST(regexp_replace(p.ancestry, '.*\/(\d+)$', '\1') AS BIGINT)
        WHEN p.ancestry ~ '^\d+$' THEN
            CAST(p.ancestry AS BIGINT)
    END
);

🛡️ Error Handling

-- Function to safely extract parent ID
CREATE OR REPLACE FUNCTION extract_parent_id(ancestry_text TEXT)
RETURNS BIGINT AS $$
BEGIN
    IF ancestry_text IS NULL OR ancestry_text = '' THEN
        RETURN NULL;
    END IF;

    IF ancestry_text ~ '.*\/(\d+)$' THEN
        RETURN CAST(regexp_replace(ancestry_text, '.*\/(\d+)$', '\1') AS BIGINT);
    ELSIF ancestry_text ~ '^\d+$' THEN
        RETURN CAST(ancestry_text AS BIGINT);
    ELSE
        RETURN NULL;
    END IF;
EXCEPTION 
    WHEN OTHERS THEN
        RETURN NULL;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- Usage
SELECT p.*, parent.name AS parent_name
FROM projects p
LEFT JOIN projects parent ON extract_parent_id(p.ancestry) = parent.id;

📊 Monitoring and Maintenance

-- Query to analyze ancestry data quality
SELECT 
    'Total Projects' AS metric,
    COUNT(*) AS count
FROM projects

UNION ALL

SELECT 
    'Projects with Ancestry' AS metric,
    COUNT(*) AS count
FROM projects 
WHERE ancestry IS NOT NULL

UNION ALL

SELECT 
    'Valid Ancestry Format' AS metric,
    COUNT(*) AS count
FROM projects 
WHERE ancestry ~ '^(\d+)(\/\d+)*$'

UNION ALL

SELECT 
    'Orphaned Projects' AS metric,
    COUNT(*) AS count
FROM projects p
WHERE p.ancestry IS NOT NULL
AND extract_parent_id(p.ancestry) NOT IN (SELECT id FROM projects);

📝 Conclusion

Working with ancestry data in PostgreSQL requires careful handling of string manipulation, type conversion, and edge cases. By following the techniques outlined in this guide, you can:

🎯 Key Takeaways

🔍 Use robust regex patterns to handle different ancestry formats
🛡️ Always handle edge cases like NULL values and malformed strings
⚡ Consider performance implications and use appropriate indexing
✅ Implement data validation to maintain ancestry integrity
🔧 Create reusable functions for complex extraction logic

💡 Final Recommendations

🎯 Test thoroughly with various ancestry formats
📊 Monitor query performance and optimize as needed
🔄 Consider alternative approaches like ltree for complex hierarchies
📚 Document your ancestry format for team members
🛠️ Implement proper error handling in production code

The techniques demonstrated here provide a solid foundation for working with hierarchical data in PostgreSQL. Whether you’re building organizational charts, category trees, or project hierarchies, these patterns will help you extract and manipulate ancestry data effectively and reliably! 🚀

📖 Additional Resources

PostgreSQL String Functions Documentation
PostgreSQL ltree Extension (for advanced hierarchical data)
Regular Expressions in PostgreSQL
PostgreSQL Performance Tips

Understanding the Array Aggregation Function in PostgreSQL (array_agg)

PostgreSQL, also known as Postgres, is a powerful and feature-rich relational database management system. One of its notable features is the array aggregation function, array_agg, which allows you to aggregate values from multiple rows into a single array. In this blog post, we’ll explore how array_agg works, its applications, and considerations for performance.

How Does `array_agg` Work?

The array_agg function takes an expression as an argument and returns an array containing the values of that expression for all the rows that match the query. Let’s illustrate this with an example.

Consider a table called employees with columns id, name, and department. Suppose we want to aggregate all the names of employees belonging to the “Sales” department into an array. We can achieve this using the following query:

SELECT array_agg(name) AS sales_employees
FROM employees
WHERE department = 'Sales';

The result of this query will be a single row with a column named sales_employees, which contains an array of all the names of employees in the “Sales” department.

Usage of `array_agg` with Subqueries

The ability to get an array as the output opens up various possibilities, especially when used in subqueries. You can leverage this feature to aggregate data from related tables or filter results based on complex conditions.

For instance, imagine you have two tables, orders and order_items, where each order can have multiple items. You want to retrieve a list of orders along with an array of item names for each order. The following query achieves this:

SELECT o.order_id, (
  SELECT array_agg(oi.item_name)
  FROM order_items oi
  WHERE oi.order_id = o.order_id
) AS item_names
FROM orders o;

In this example, the subquery within the main query’s select list utilizes array_agg to aggregate item names from the order_items table, specific to each order.

Complex Query Example Using `array_agg`

To demonstrate a more complex scenario, let’s consider a database that stores books and their authors. We have three tables: books, authors, and book_authors (a join table that associates books with their respective authors).

Suppose we want to retrieve a list of books along with an array of author names for each book by alphabetical order. We can achieve this using a query that involves joins and array_agg:

SELECT b.title, array_agg(a.author_name ORDER BY a.author_name ASC) AS authors
FROM books b
JOIN book_authors ba ON b.book_id = ba.book_id
JOIN authors a ON ba.author_id = a.author_id
GROUP BY b.book_id;

In this query, we join the tables based on their relationships and use array_agg to aggregate author names into an array for each book. The GROUP BY clause ensures that each book’s array of author names is grouped correctly.

Performance Considerations

While array_agg is a powerful function, it’s essential to consider its performance implications, especially when working with large datasets. Aggregating values into arrays can be computationally intensive, and the resulting array can consume significant memory.

If you anticipate working with large result sets or complex queries involving array_agg, it’s worth optimizing your database schema, indexing relevant columns, and analyzing query performance using PostgreSQL’s built-in tools.

Additionally, consider whether array_agg is the most efficient solution for your specific use case. Sometimes, alternative approaches, such as using temporary tables or custom aggregate functions, might offer better performance.

Conclusion

The array_agg function in PostgreSQL provides a powerful mechanism for aggregating values into arrays. It offers flexibility and opens up opportunities for various applications, including subqueries and complex data manipulations. However, when working with large datasets, it’s crucial to be mindful of potential performance implications and explore optimization strategies accordingly.

PostgreSQL commands to remember

List of commands to remember using postgres DB managment system.

# login to psql client
psql postgres # OR
psql -U postgres
create database mydb; # create db
create user abhilash with SUPERUSER CREATEDB CREATEROLE encrypted password 'abhilashPass!'; 
grant all privileges on database mydb to myuser; # add privileges

Connect to DB, List tables and users, functions, views, schema

\l # lists all the databases
\c dbname # connect to db
\dt # show tables
\d table_name # Describe a table
\dn # List available schema
\df #  List available functions
\dS [your_table_name] # List triggers
\dv # List available views
\du # lists all user accounts and roles 
\du+ # is the extended version which shows even more information.

Show history, save to file, edit using editor, execution time, help

SELECT version(); # version of psql
\g  # Execute the previous command
\s # Command history
\s filename # save Command history to a file
\i filename # Execute psql commands from a file
\? # help on psql commands
\h ALTER TABLE # To get help on specific PostgreSQL statement
\timing #  Turn on/off query execution time
\e # Edit command in your own editor
\e [function_name] # It is more useful when you edit a function in the editor. Do \df for functions
\o [file_name] # send all next query results to file
    \o out.txt
    \dt 
    \o # switch
    \dt

Change output, Quit psql

# Switch output options
\a command switches from aligned to non-aligned column output.
\H command formats the output to HTML format.
\q # quit psql

Reference: https://www.postgresqltutorial.com/postgresql-administration/psql-commands/

PostgreSQL Cheat Sheet

CREATE DATABASE

CREATE DATABASE dbName;

CREATE TABLE (with auto numbering integer id)

CREATE TABLE tableName (
 id serial PRIMARY KEY,
 name varchar(50) UNIQUE NOT NULL,
 dateCreated timestamp DEFAULT current_timestamp
);

Add a primary key

ALTER TABLE tableName ADD PRIMARY KEY (id);

Create an INDEX

CREATE UNIQUE INDEX indexName ON tableName (columnNames);

Backup a database (command line)

pg_dump dbName > dbName.sql

Backup all databases (command line)

pg_dumpall > pgbackup.sql

Run a SQL script (command line)

psql -f script.sql databaseName

Search using a regular expression

SELECT column FROM table WHERE column ~ 'foo.*';

The first N records

SELECT columns FROM table LIMIT 10;

Pagination

SELECT cols FROM table LIMIT 10 OFFSET 30;

Prepared Statements

PREPARE preparedInsert (int, varchar) AS
  INSERT INTO tableName (intColumn, charColumn) VALUES ($1, $2);
EXECUTE preparedInsert (1,'a');
EXECUTE preparedInsert (2,'b');
DEALLOCATE preparedInsert;

Create a Function

CREATE OR REPLACE FUNCTION month (timestamp) RETURNS integer 
 AS 'SELECT date_part(''month'', $1)::integer;'
LANGUAGE 'sql';

Table Maintenance

VACUUM ANALYZE table;

Reindex a database, table or index

REINDEX DATABASE dbName;

Show query plan

EXPLAIN SELECT * FROM table;

Import from a file

COPY destTable FROM '/tmp/somefile';

Show all runtime parameters

SHOW ALL;

Grant all permissions to a user

GRANT ALL PRIVILEGES ON table TO username;

Perform a transaction

BEGIN TRANSACTION 
 UPDATE accounts SET balance += 50 WHERE id = 1;
COMMIT;

Basic SQL

Get all columns and rows from a table

SELECT * FROM table;

Add a new row

INSERT INTO table (column1,column2)
VALUES (1, 'one');

Update a row

UPDATE table SET foo = 'bar' WHERE id = 1;

Delete a row

DELETE FROM table WHERE id = 1;

From: https://www.petefreitag.com/cheatsheets/postgresql/

PostgreSQL 9.3 : Installation on ubuntu 14.04

Hi guys, I just started installing postgres on my ubuntu VM. I referred some docs, and followed this one: https://www.digitalocean.com/community/tutorials/how-to-install-and-use-postgresql-on-ubuntu-14-04

Its pretty much explained in this page. But just explaining here the important things.

You can install postgres by ubuntu’s own apt packaging system. Update local apt repository.

$ sudo apt-get update
$ sudo apt-get install postgresql postgresql-contrib

Postgres uses role based access for the unix users. After the installation a default role called ‘postgres’ will be created. You can login to postgres account and start using or creating new roles with Postgres.

$ sudo -i -u postgres

Access the postgres console by

$ psql

But i cannot enter into the console and I got the following error:

postgres@8930a29k5d05:/home/rails/my_project$ psql
psql: could not connect to server: No such file or directory
        Is the server running locally and accepting
        connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

What could be the reason for this error?

So just gone through Postgres doc (http://www.postgresql.org/docs/9.3/static/server-start.html). You can see the same error under the section 17.3.2. Client Connection Problems. But the solution is not mentioned.

Original Reason: PostgreSQL Server was not running after the installation.

I tried rebooting the system and via init script the server should run automatically. But the server is not running again. I understood that something prevents postgres from running the server. What is it?

Just check your postgres server is running or not

$ sudo -aux | grep post
postgres@8930a29k5d05:/home/rails/my_project$ ps -aux | grep postgres
root       136  0.0  0.2  47124  3056 ?        S    06:10   0:00 sudo -u postgres -s
postgres   137  0.0  0.3  18164  3220 ?        S    06:10   0:00 /bin/bash
postgres   140  0.0  0.2  15572  2192 ?        R+   06:10   0:00 ps -aux
postgres   141  0.0  0.0   4892   336 ?        R+   06:10   0:00 grep post

The server is not running.

Run the server manually by

root@8930a29k5d05:/home/rails/my_project#  /etc/init.d/postgresql start
 * Starting PostgreSQL 9.3 database server
                                                                                                                                                         [ OK ] 
root@8930a29k5d05:/home/rails/my_project# ps aux | grep postgres
postgres   158  0.1  2.0 244928 20752 ?        S    06:28   0:00 /usr/lib/postgresql/9.3/bin/postgres -D /var/lib/postgresql/9.3/main -c config_file=/etc/postgresql/9.3/main/postgresql.conf
postgres   160  0.0  0.3 244928  3272 ?        Ss   06:28   0:00 postgres: checkpointer process

postgres   161  0.0  0.4 244928  4176 ?        Ss   06:28   0:00 postgres: writer process

postgres   162  0.0  0.3 244928  3272 ?        Ss   06:28   0:00 postgres: wal writer process

postgres   163  0.0  0.5 245652  6000 ?        Ss   06:28   0:00 postgres: autovacuum launcher process

postgres   164  0.0  0.3 100604  3336 ?        Ss   06:28   0:00 postgres: stats collector process

root       178  0.0  0.0   8868   884 ?        S+   06:28   0:00 grep --color=auto post
root@8930a29k5d05:/home/rails/my_project#

Now the server starts running. If still not works, then try to reconfigure your locales as mentioned here

$ dpkg-reconfigure locales

It is strange that, after installing such a popular database software, it doesn’t provide any information regarding the failure of its own server. It should give the developers some clue so that they can save their precious time.

The reason of this failure, what I concluded is
1. After installation we have to run the server manually
OR
2. I tried resetting the locales (So if no locales set in the machine may prevented the postgres from starting automatically?)