Basic SELECT Queries in PostgreSQL: Retrieving Data Efficiently

Retrieving data is one of the most fundamental operations in any database system. PostgreSQL provides the SELECT statement to fetch data from tables with powerful filtering, sorting, and aggregation options. In this guide, we’ll explore how to use SELECT queries effectively with the customers table.

1. Retrieving All Columns

To retrieve all columns from the customers table, use the * wildcard:

SELECT * FROM customers;

Best Practices:

• Only use SELECT * when necessary, as fetching unnecessary data can impact performance.

2. Selecting Specific Columns

For better performance and readability, specify only the needed columns:

SELECT first_name, last_name, email FROM customers;

3. Filtering Data with WHERE Clause

The WHERE clause allows you to filter records based on conditions.

Example: Find a customer by email

SELECT * FROM customers WHERE email = 'john.doe@example.com';

Example: Find customers born after 1990

SELECT first_name, last_name, date_of_birth FROM customers WHERE date_of_birth > '1990-01-01';

4. Sorting Data with ORDER BY

You can sort query results using ORDER BY:

SELECT first_name, last_name FROM customers ORDER BY last_name ASC;

Example: Sort by birthdate in descending order

SELECT first_name, last_name, date_of_birth FROM customers ORDER BY date_of_birth DESC;

5. Limiting and Paginating Results

To limit the number of rows returned, use LIMIT:

SELECT * FROM customers LIMIT 5;

For pagination, combine LIMIT and OFFSET:

SELECT * FROM customers ORDER BY customer_id LIMIT 10 OFFSET 20;

6. Using Aliases for Better Readability

Aliases (AS) rename columns or tables in the result set.

SELECT first_name AS fname, last_name AS lname FROM customers;

7. Combining Conditions with AND, OR, and NOT

Example: Find customers from a specific city and born after 1985

SELECT * FROM customers WHERE address LIKE '%New York%' AND date_of_birth > '1985-01-01';

Example: Find customers without phone numbers

SELECT * FROM customers WHERE phone IS NULL;

8. Pattern Matching with LIKE and ILIKE

- LIKE` is case-sensitive.
- ILIKE` is case-insensitive.

SELECT * FROM customers WHERE email LIKE '%@gmail.com';

9. Aggregation Functions (COUNT, AVG, MAX, MIN, SUM)

Example: Count total customers

SELECT COUNT(*) FROM customers;

Example: Find the oldest customer

SELECT MIN(date_of_birth) FROM customers;

Conclusion

The SELECT statement is the foundation of data retrieval in PostgreSQL. By using filtering, sorting, aggregation, and pagination techniques, you can efficiently query your database. In the next tutorial, we will dive deeper into advanced SELECT operations, including joins and subqueries.