Best PostgreSQL Queries/Functions for Daily Operations

Here are some useful PostgreSQL queries and functions, along with hands-on examples and descriptions:

1. Basic SELECT Query

Query:

SELECT * FROM employees;

Description:

The SELECT query is used to retrieve data from a database. In this case, it retrieves all columns (*) from the employees table.

2. Filtering with WHERE

Query:

SELECT name, department FROM employees WHERE salary > 50000;

Description:

The WHERE clause filters rows based on a condition. This query retrieves the names and departments of employees whose salary is greater than 50,000.

3. Sorting Results with ORDER BY

Query:

SELECT name, salary FROM employees ORDER BY salary DESC;

Description:

ORDER BY sorts the result set. DESC is used to sort in descending order. Here, the employee names and their salaries are sorted from highest to lowest salary.

4. Aggregating Data with COUNT()

Query:

SELECT COUNT(*) FROM employees WHERE department = 'Sales';

Description:

COUNT() returns the number of rows that match the condition. This query counts how many employees belong to the 'Sales' department.

5. Grouping Data with GROUP BY

Query:

SELECT department, AVG(salary) AS average_salary FROM employees GROUP BY department;

Description:

GROUP BY groups rows that have the same values in the specified column(s). AVG() calculates the average salary for each department.

6. JOIN Queries

Query:

SELECT employees.name, departments.department_name 
FROM employees
JOIN departments ON employees.department_id = departments.id;

Description:

JOIN is used to combine rows from two or more tables based on a related column. This query fetches employee names along with their corresponding department names by joining employees and departments tables.

7. Subqueries

Query:

SELECT name, salary FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);

Description:

A subquery is a query nested inside another query. Here, it retrieves employees who earn more than the average salary.

8. Window Functions (ROW_NUMBER)

Query:

SELECT name, salary, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rank FROM employees;

Description:

ROW_NUMBER() assigns a unique sequential integer to rows within a result set. This query ranks employees based on their salary, from highest to lowest.

9. Common Table Expressions (CTE)

Query:

WITH HighEarners AS (
    SELECT name, salary FROM employees WHERE salary > 60000
)
SELECT * FROM HighEarners;

Description:

A CTE is a temporary result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE. This query defines a CTE called HighEarners to select all employees with a salary greater than 60,000.

10. UPSERT (INSERT or UPDATE)

Query:

INSERT INTO employees (id, name, salary) 
VALUES (1, 'John Doe', 55000)
ON CONFLICT (id) 
DO UPDATE SET salary = EXCLUDED.salary;

Description:

UPSERT (Update + Insert) is useful for inserting a row or updating it if it already exists. Here, if an employee with id = 1 exists, their salary is updated; otherwise, a new employee is inserted.

These queries cover a wide range of common tasks in PostgreSQL, from basic data retrieval and filtering to advanced operations like window functions and UPSERT.

11. LIMIT & OFFSET for Pagination

Query:

SELECT * FROM products ORDER BY product_name LIMIT 10 OFFSET 20;

Description:

LIMIT and OFFSET are used to paginate query results. This query retrieves 10 products, starting from the 21st row (OFFSET 20).

12. CASE for Conditional Logic

Query:

SELECT name, salary,
    CASE 
        WHEN salary > 70000 THEN 'High'
        WHEN salary BETWEEN 50000 AND 70000 THEN 'Medium'
        ELSE 'Low'
    END AS salary_range
FROM employees;

Description:

The CASE statement adds conditional logic to your queries. Here, employees are categorized based on their salary into "High," "Medium," or "Low" salary ranges.

13. DISTINCT to Remove Duplicates

Query:

SELECT DISTINCT department FROM employees;

Description:

DISTINCT ensures that only unique values are returned. This query retrieves unique department names from the employees table.

14. COALESCE to Handle NULL Values

Query:

SELECT name, COALESCE(phone, 'N/A') AS phone FROM employees;

Description:

COALESCE returns the first non-NULL value in a list. This query returns the employee's phone number, or 'N/A' if the phone number is NULL.

15. String Manipulation with CONCAT()

Query:

SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM employees;

Description:

CONCAT() combines strings into a single result. This query concatenates the first name and last name into a single full name.

16. Subquery in SELECT Clause

Query:

SELECT name, (SELECT department_name FROM departments WHERE id = employees.department_id) AS department_name
FROM employees;

Description:

This query uses a subquery within the SELECT clause to fetch the department name for each employee, instead of using a JOIN.

17. DATE and TIME Functions

Query:

SELECT name, hire_date, AGE(hire_date) AS years_worked FROM employees;

Description:

The AGE() function calculates the difference between two dates. This query retrieves the number of years an employee has worked based on their hire_date.

18. Array Functions: ANY()

Query:

SELECT name FROM employees WHERE department_id = ANY(ARRAY[1, 2, 3]);

Description:

ANY() compares a value to any value in an array. This query retrieves the names of employees who belong to departments with IDs 1, 2, or 3.

19. String Pattern Matching with LIKE and ILIKE

Query:

SELECT name FROM employees WHERE name LIKE 'A%';

Description:

LIKE is used for pattern matching. This query returns all employees whose name starts with "A." If you want case-insensitive matching, use ILIKE.

20. HAVING with GROUP BY

Query:

SELECT department, COUNT(*) AS num_employees
FROM employees
GROUP BY department
HAVING COUNT(*) > 10;

Description:

The HAVING clause is used to filter groups based on aggregate functions. This query retrieves departments that have more than 10 employees.

These examples cover more advanced techniques such as handling NULL values, working with arrays, pattern matching, and using aggregate functions with HAVING, all of which are highly practical in day-to-day database work.