PostgreSQL Fetch Data
Learn how to retrieve data from PostgreSQL tables
🔍 What is Fetching Data?
Fetching data means retrieving information from database tables using SELECT queries. It's the most common database operation, allowing you to read and display stored information efficiently.
-- Fetch all users
SELECT * FROM users;
Output:
| id | name | |
|---|---|---|
| 1 | John Doe | [email protected] |
Key SELECT Concepts
Select All
Retrieve all columns from table
SELECT * FROM products;Specific Columns
Choose which columns to fetch
SELECT name, price
FROM products;WHERE Clause
Filter results with conditions
SELECT * FROM products
WHERE price > 100;ORDER BY
Sort results in order
SELECT * FROM products
ORDER BY price DESC;🔹 Select All Data
The asterisk (*) selects all columns from a table. This is useful for viewing complete records. However, in production code, it's better to specify exact columns for performance and clarity.
-- Fetch all columns and rows
SELECT * FROM employees;
Output:
| emp_id | first_name | last_name | salary |
|---|---|---|---|
| 1 | Alice | Johnson | 75000 |
🔹 Select Specific Columns
Specify column names to retrieve only the data you need. This improves query performance by reducing data transfer. It also makes your queries more readable and maintainable for future developers.
-- Fetch only name and email columns
SELECT first_name, last_name, email
FROM employees;
Output:
| first_name | last_name | |
|---|---|---|
| Alice | Johnson | [email protected] |
🔹 Filter with WHERE Clause
The WHERE clause filters rows based on conditions. You can use comparison operators like equals, greater than, less than, and logical operators like AND, OR. This helps you find exactly the data you need.
-- Fetch products with price greater than 50
SELECT product_name, price
FROM products
WHERE price > 50;
-- Multiple conditions
SELECT product_name, price, stock_quantity
FROM products
WHERE price > 50 AND stock_quantity > 0;
Output:
| product_name | price | stock_quantity |
|---|---|---|
| Laptop | 999.99 | 25 |
🔹 Sort Results with ORDER BY
ORDER BY sorts query results in ascending or descending order. Use ASC for ascending (default) or DESC for descending. You can sort by multiple columns to create complex ordering logic.
-- Sort by price in descending order
SELECT product_name, price
FROM products
ORDER BY price DESC;
-- Sort by multiple columns
SELECT first_name, last_name, salary
FROM employees
ORDER BY salary DESC, last_name ASC;
Output:
| product_name | price |
|---|---|
| Laptop | 999.99 |
| Keyboard | 79.99 |
🔹 Limit Results
LIMIT restricts the number of rows returned by a query. OFFSET skips a specified number of rows before returning results. These are essential for implementing pagination in applications and improving query performance.
-- Get first 5 products
SELECT product_name, price
FROM products
ORDER BY price DESC
LIMIT 5;
-- Pagination: skip first 10, get next 5
SELECT product_name, price
FROM products
ORDER BY product_id
LIMIT 5 OFFSET 10;
Output:
✓ 5 rows returned
🔹 Pattern Matching with LIKE
The LIKE operator searches for patterns in text columns. Use % for any sequence of characters and _ for a single character. This is powerful for flexible text searches and filtering operations.
-- Find products starting with 'Lap'
SELECT product_name, price
FROM products
WHERE product_name LIKE 'Lap%';
-- Find emails ending with 'company.com'
SELECT first_name, email
FROM employees
WHERE email LIKE '%company.com';
Output:
| product_name | price |
|---|---|
| Laptop | 999.99 |