PostgreSQL WHERE

PostgreSQL WHERE Clause

The WHERE clause is used to aggressively filter records in your database. It allows you to extract only those records that fulfill a specified condition.

Without a WHERE clause, you would constantly download the entire table. This clause acts as the gatekeeper for retrieving exact, precise data points.

The Basic Syntax

The WHERE clause is placed immediately after the FROM clause. It consists of a column name, an operator, and a target value to match against.

When the database processes the query, it evaluates the condition per row. If the row returns TRUE for the condition, it gets included in the result.

SELECT column1, column2
FROM table_name
WHERE condition;

Filtering Text Values

When evaluating text or string values, SQL requires single quotes. You must wrap your target string in standard single quotation marks.

If you forget the single quotes, PostgreSQL assumes you mean a column name. This will immediately result in a syntax error during query execution.

-- Fetch only the customers who reside in Mexico
SELECT * FROM Customers
WHERE Country = 'Mexico';

Filtering Numeric Values

Unlike text fields, numeric fields should not be enclosed in quotes. You supply the raw number directly into the specific WHERE condition.

You can use various operators like greater than, less than, and equals. This allows for dynamic range filtering on pricing, age, or quantity columns.

-- Fetch products that cost exactly 18.00
SELECT * FROM Products
WHERE Price = 18.00;

Using Multiple Conditions

A single WHERE clause can chain together multiple different conditions. You achieve this by utilizing the AND and OR logical operators.

This enables you to pinpoint incredibly specific rows of data accurately. For instance, you can find a product with a specific price in a specific category.

Summary

The WHERE clause is an absolute necessity for database performance. It prevents your application from crashing by downloading too much unneeded data.

Always remember to use single quotes for text, and no quotes for raw numbers.