How to Write a SELECT Statement in PostgreSQL

The SELECT statement is the most frequently used SQL command. It retrieves rows from one or more tables and lets you choose exactly which columns to return.

Basic SELECT Syntax

-- Select specific columns
SELECT first_name, last_name, email
FROM customers;

-- Select all columns
SELECT *
FROM customers;

-- Select with an alias
SELECT first_name AS name, email AS contact
FROM customers;

The query above asks PostgreSQL to return the first_name, last_name, and email columns from the customers table. Using * returns every column, which is handy for exploration but should be avoided in production queries for performance reasons.

Limiting Results with LIMIT

-- Return only the first 10 rows
SELECT first_name, email
FROM customers
LIMIT 10;

-- Skip the first 20 rows, then return 10
SELECT first_name, email
FROM customers
LIMIT 10 OFFSET 20;

LIMIT caps the number of rows returned, and OFFSET skips a given number of rows before starting to return results. Together they are the foundation of pagination.

Removing Duplicates with DISTINCT

-- Unique cities from the customers table
SELECT DISTINCT city
FROM customers;

-- Distinct on multiple columns
SELECT DISTINCT city, country
FROM customers;

Common Use Cases

• Displaying a list of records in a UI table or dashboard
• Exporting subsets of data for reporting
• Paginating through large datasets with LIMIT and OFFSET
• Exploring a table's structure and sample data during development