Basic SELECT Queries

You've built tables, inserted rows, and maybe even updated a record or two. But the real power of PostgreSQL comes when you need to answer questions — and that means pulling data back out. If your only tool is SELECT * FROM table;, you're driving a sports car in first gear. This lesson transforms you from a data writer into a data reader, teaching you how to retrieve data with basic SELECT queries using filters, sorting, and column selection — the exact skills you'll use in every single query from here on out.

The problem this lesson solves

Picture this: you've just loaded 10,000 customer records into a customers table. Your manager asks, "How many customers signed up last month?" You open psql, type SELECT * FROM customers;, and get a wall of text that scrolls for minutes. Sound familiar? This is the core pain — raw SELECT queries are great for exploration, but terrible for answers. You need precision: the right columns, the right rows, and a sensible order. Without it, you're drowning in data instead of extracting insight.

This lesson gives you the three levers that turn a blunt query into a surgical one: projection (choosing columns), filtering (choosing rows), and ordering (choosing sequence). These are foundational skills that make every future lesson — joins, aggregates, indexes — actually useful.

Core concept / mental model

Think of PostgreSQL as a giant vending machine. You insert coins (data) into the machine, but to get your snack, you press a button — that's the SELECT query. The machine doesn't dump everything at you; it gives you exactly what the button (your query) asks for.

A SELECT query has a logical flow, even if you write it in a single line:

1. FROM — which vending machine (table) are you using?
2. WHERE — which snacks (rows) match your criteria?
3. SELECT — which details (columns) do you want to see?
4. ORDER BY — in what order should they come out?

This mental model helps you read any SELECT query left to right, even complex ones.

💡 Pro tip: SQL is declarative — you tell PostgreSQL what you want, not how to get it. The database engine figures out the best way to fetch those rows for you.

How it works step by step

Let's break down the anatomy of a basic SELECT statement:

SELECT column1, column2, ...
FROM table_name
WHERE condition
ORDER BY column_name ASC | DESC;

Each clause has a specific role:

• SELECT — Lists the columns (or expressions) to return.
• FROM — Specifies the source table.
• WHERE — Filters rows before returning (only rows where the condition is true).
• ORDER BY — Sorts the final result set.

Here's the execution order (what happens behind the scenes):

1. FROM loads the table.
2. WHERE filters rows.
3. SELECT picks the columns.
4. ORDER BY sorts the result.

This is why you can't use a column alias in WHERE but can in ORDER BY — the alias is only created in step 3.

⚠️ Watch out: Omitting WHERE returns all rows — great for small tables, dangerous for big ones. Always ask: "Do I really need every row?"

Hands-on walkthrough

Let's get our hands dirty. First, create a sample table and insert some data:

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name VARCHAR(50),
  category VARCHAR(30),
  price NUMERIC(8,2)
);

INSERT INTO products (name, category, price) VALUES
('Laptop', 'Electronics', 999.99),
('Mouse', 'Electronics', 29.99),
('Desk', 'Furniture', 249.00),
('Chair', 'Furniture', 129.50),
('Monitor', 'Electronics', 299.99);

Now, let's retrieve data with basic SELECT queries step by step.

1. Select specific columns

SELECT name, price FROM products;

Output:

   name   | price
----------+--------
 Laptop   | 999.99
 Mouse    |  29.99
 Desk     | 249.00
 Chair    | 129.50
 Monitor  | 299.99
(5 rows)

2. Filter rows with WHERE

Find all electronics under $300:

SELECT name, price
FROM products
WHERE category = 'Electronics' AND price < 300;

Output:

  name   | price
---------+--------
 Mouse   |  29.99
 Monitor | 299.99
(2 rows)

3. Sort results

Order by price from cheapest to most expensive:

SELECT name, price
FROM products
ORDER BY price ASC;

Output:

   name   | price
----------+--------
 Mouse    |  29.99
 Chair    | 129.50
 Desk     | 249.00
 Monitor  | 299.99
 Laptop   | 999.99
(5 rows)

4. Combine everything

Retrieve the second cheapest product in the Furniture category:

SELECT name, price
FROM products
WHERE category = 'Furniture'
ORDER BY price ASC
LIMIT 1 OFFSET 1;

Output:

 name  | price
-------+--------
 Desk  | 249.00
(1 row)

💡 Pro tip: LIMIT and OFFSET are great for pagination, but be careful — they're not the most efficient on huge tables. For production pagination, consider keyset pagination (covered in a later lesson).

Compare options / when to choose what

When retrieving data, you have several ways to shape your result. Here's a quick comparison:

Variations: alternative approaches

• SELECT DISTINCT — Removes duplicate rows from the result. Useful when you want a list of unique categories, for example.
• WHERE IN (list) — Shorthand for multiple OR conditions: WHERE category IN ('Electronics', 'Furniture').
• String pattern matching — Use LIKE with % wildcards: WHERE name LIKE 'Mo%' finds all products starting with 'Mo'.

⚠️ Note: LIKE is case-sensitive in PostgreSQL; use ILIKE for case-insensitive matching.

Troubleshooting & edge cases

Even simple SELECTs can trip you up. Here are the most common issues you'll face:

1. "Column does not exist" error

ERROR:  column "category" does not exist

Cause: You quoted the column name in the CREATE TABLE or your column name has uppercase letters. PostgreSQL folds unquoted identifiers to lowercase.

Fix: Check your table schema with \d products. Use lowercase column names without quotes.

2. Empty result set

Your query returns 0 rows, but you expected data.

Cause: The filter condition is too strict, or you're comparing numbers to strings.

Fix: Try removing filters one by one. Also check for type mismatches — WHERE price = '29.99' may not match if the column is NUMERIC.

3. ORDER BY doesn't sort as expected

Your results are in a random-looking order.

Cause: You didn't add ORDER BY at all — PostgreSQL doesn't guarantee any order without it.

Fix: Always add ORDER BY when order matters. Remember that NULL values sort last by default in ascending order.

4. Performance is slow

A query on a large table takes forever.

Cause: You're selecting all columns and doing a full table scan.

Fix: - Select only the columns you need. - Add a WHERE clause to limit rows. - Later lessons will show you how to add indexes to speed up filtering.

5. Quoting mistakes

You used double quotes around a string literal instead of single quotes.

SELECT * FROM products WHERE name = "Laptop"; -- WRONG

Fix: In PostgreSQL, string literals use single quotes. Double quotes are for identifiers (like table or column names).

💡 Pro tip: When you're not sure if a column name is valid, use \d table_name in psql to see the exact schema.

What you learned & what's next

Let's recap what we've covered:

• The core idea — A SELECT query is your window into the database; you control what columns, rows, and order you see.
• Anatomy — SELECT, FROM, WHERE, and ORDER BY each have a specific role and execution order.
• Hands-on skills — You can now write queries that filter with multiple conditions, sort results, and limit rows.
• Troubleshooting — You know how to fix common errors like missing columns, empty results, and ordering issues.

You're now ready to take this one step further. The next lesson in this track will show you how to filter more powerfully with WHERE clauses — including logical operators like AND, OR, and NOT, plus IN and BETWEEN. That's where your queries start to get truly expressive.

🔥 Your next challenge: Try writing a query that retrieves the top 3 most expensive products in each category. You'll need a subquery or window function — spoiler: we'll get to those soon!

Keep practicing, and before you know it, retrieving data with basic SELECT queries will feel as natural as breathing.