PostgreSQL WHERE Clause for Beginners: Filter Data

How do you filter data in PostgreSQL? You use the WHERE clause. WHERE is a small piece of an SQL query that tells PostgreSQL, "only give me the rows that match this rule." Without it, a query returns every row in the table. With it, you get just the rows you care about.

In this beginner guide you will learn how the PostgreSQL WHERE clause works, one step at a time. You will filter by numbers and text, combine rules with AND and OR, and use IN, BETWEEN, ILIKE and IS NULL. Every term is explained the first time we use it, and every example is small enough to copy and run.

What is the WHERE clause?

A table in PostgreSQL is like a spreadsheet. It has columns (the headings) and rows (the records). A query is a question you ask the database. A clause is one part of a query, such as SELECT, FROM or WHERE.

The WHERE clause sits after FROM. It holds a condition, which is a rule that is either true or false for each row. PostgreSQL checks every row, keeps the rows where the rule is true, and throws away the rest.

Set up a practice table

To follow along, open psql (the PostgreSQL command line tool) and paste this. It creates a small table of products and adds six rows.

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  category TEXT NOT NULL,
  price NUMERIC(8, 2) NOT NULL,
  stock INTEGER NOT NULL,
  discontinued_on DATE
);

INSERT INTO products (name, category, price, stock, discontinued_on) VALUES
  ('Laptop', 'electronics', 899.00, 12, NULL),
  ('Phone', 'electronics', 599.50, 0, NULL),
  ('Desk', 'furniture', 150.00, 5, NULL),
  ('Chair', 'furniture', 85.99, 20, '2026-01-15'),
  ('Notebook', 'stationery', 3.25, 200, NULL),
  ('Pen', 'stationery', 1.10, 500, NULL);

The first statement creates the table with six columns. SERIAL gives each row an automatic id number. NUMERIC(8, 2) stores prices with two decimals. The second statement adds the rows. Notice that Laptop has NULL for discontinued_on, which means "no value". If you need a refresher, read our guide on how to create a table in PostgreSQL.

Your first WHERE query

SELECT name, price
FROM products
WHERE category = 'furniture';

Line 1 picks the columns name and price. Line 2 says which table to read. Line 3 is the filter: keep only rows where category equals 'furniture'. The result is Desk and Chair. Notice that text values go inside single quotes. Numbers do not need quotes.

Comparison operators

An operator is a symbol that compares two values. Here are the ones you will use most.

SELECT name, price
FROM products
WHERE price > 500;

This keeps rows where price is more than 500. You get Laptop and Phone. Be careful: a single equals sign (=) means "is equal to" in SQL. You do not write two equals signs like in JavaScript.

Combine conditions with AND and OR

Often one rule is not enough. AND means both rules must be true. OR means at least one rule must be true.

SELECT name, price, stock
FROM products
WHERE category = 'electronics'
  AND stock > 0;

A row must be in the electronics category and have stock above zero. Only Laptop passes. Phone is electronics, but its stock is 0, so AND rejects it.

SELECT name, category
FROM products
WHERE category = 'furniture'
  OR price < 5;

Now a row passes if it is furniture or if its price is under 5. You get Desk, Chair, Notebook and Pen.

Use parentheses to avoid surprises

AND is checked before OR, which can give confusing results. Parentheses group rules so you control the order, just like in math.

SELECT name, category, price
FROM products
WHERE (category = 'furniture' OR category = 'stationery')
  AND price < 100;

First PostgreSQL checks the part in parentheses: furniture or stationery. Then it also requires the price to be under 100. The result is Chair, Notebook and Pen. Desk is furniture but costs 150, so it is removed.

IN: match any value in a list

SELECT name, category
FROM products
WHERE category IN ('furniture', 'stationery');

IN is a shorter way to write several OR rules on the same column. It keeps a row if category matches any value in the list. Use NOT IN to do the opposite.

BETWEEN: match a range

SELECT name, price
FROM products
WHERE price BETWEEN 50 AND 200;

BETWEEN keeps values from 50 up to 200, and it includes both ends. You get Desk (150.00) and Chair (85.99). It works for numbers, dates and text.

Search text with LIKE and ILIKE

When you do not know the full text, use LIKE with a wildcard. The percent sign (%) stands for "any characters". In PostgreSQL, LIKE cares about upper and lower case. ILIKE is the same but ignores case.

SELECT name
FROM products
WHERE name ILIKE 'n%';

This finds names that start with the letter n, in any case. You get Notebook. To find a word anywhere inside the text, put % on both sides, for example '%book%'.

IS NULL: find missing values

NULL means "no value" or "unknown". It is not the same as zero or an empty string. Because of this, you cannot test it with an equals sign. Writing discontinued_on = NULL never matches anything. Use IS NULL instead.

SELECT name
FROM products
WHERE discontinued_on IS NULL;

SELECT name
FROM products
WHERE discontinued_on IS NOT NULL;

The first query returns products that are still sold. The second returns products that have a discontinued date, which is only Chair. We explain this more in our guide to NULL and IS NULL.

Common beginner mistakes

• Forgetting quotes around text. Write category = 'furniture', not category = furniture.
• Using double quotes for text. In PostgreSQL, double quotes are for column and table names. Use single quotes for text values.
• Using = NULL instead of IS NULL.
• Mixing AND and OR without parentheses.
• Forgetting that LIKE is case sensitive. Use ILIKE when case should not matter.

WHERE also works with UPDATE and DELETE

WHERE is not only for SELECT. It also limits which rows UPDATE and DELETE change. This matters a lot: if you forget WHERE on a DELETE, you remove every row in the table. A good habit is to run a SELECT with the same WHERE first, check the rows, and only then change the query to UPDATE or DELETE.

Quick recap

• WHERE keeps only the rows where a condition is true.
• Use =, <>, <, >, <= and >= to compare values.
• AND needs both rules to be true. OR needs one. Use parentheses to group them.
• IN matches a list, BETWEEN matches a range, and ILIKE searches text without caring about case.
• Use IS NULL and IS NOT NULL for missing values.