PostgreSQL SELECT Data

PostgreSQL SELECT Data

The SELECT statement in PostgreSQL is used to retrieve data from tables. It is the most commonly used SQL command.

1️⃣ Basic SELECT


Select specific columns:


2️⃣ WHERE Clause (Filtering Rows)


Multiple conditions:

WHERE age > 18 AND status = 'active';

3️⃣ DISTINCT (Remove Duplicates)

SELECT DISTINCT age FROM users;

4️⃣ ORDER BY (Sorting Results)

SELECT * FROM users
ORDER BY name ASC;

Descending:

ORDER BY age DESC;

5️⃣ LIMIT & OFFSET (Pagination)

SELECT * FROM users
LIMIT 10 OFFSET 0;

6️⃣ LIKE & ILIKE (Pattern Matching)

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

Case-insensitive:

WHERE name ILIKE 'a%';

7️⃣ IN, BETWEEN, IS NULL

WHERE age IN (18, 21, 25);
WHERE age BETWEEN 20 AND 30;
WHERE email IS NULL;

8️⃣ Aggregate Functions

SELECT COUNT(*) FROM users;
SELECT AVG(age) FROM users;
SELECT MAX(age), MIN(age) FROM users;

9️⃣ GROUP BY

SELECT status, COUNT(*)
FROM users
GROUP BY status;

🔟 HAVING (Filter Groups)

SELECT status, COUNT(*)
FROM users
GROUP BY status
HAVING COUNT(*) > 5;

1️⃣1️⃣ Column Aliases

SELECT name AS full_name, email AS user_email
FROM users;

1️⃣2️⃣ SELECT with JOIN (Preview)

SELECT u.name, o.order_date
FROM users u
JOIN orders o ON u.id = o.user_id;

1️⃣3️⃣ Subquery in SELECT

SELECT name,
(SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) AS total_orders
FROM users u;

1️⃣4️⃣ Common Mistakes

❌ Missing WHERE → returns all rows
❌ Wrong column name
❌ Forgetting GROUP BY with aggregates

1️⃣5️⃣ Best Practices

✔ Avoid SELECT * in production
✔ Use indexes for filtering
✔ Use aliases for readability
✔ Limit results for large tables

You may also like...