PostgreSQL SELECT

Introduction to PostgreSQL SELECT

The SELECT statement is the most frequently used SQL command. It retrieves rows from tables, views, or the result of expressions.

Basic syntax:

SELECT select_list FROM table_name;

Selecting specific columns

List the columns you want after SELECT, separated by commas. Using explicit column names instead of * is a best practice — it makes queries self-documenting and avoids issues when table structure changes.

SELECT first_name, last_name, email FROM customer;

Selecting all columns

SELECT * FROM customer;

Use * for ad-hoc queries and exploration. Avoid it in application code — schema changes can silently break queries or return unexpected data.

Column aliases with AS

Rename columns in the result set using AS. The alias is available in ORDER BY but not in WHERE (because WHERE is evaluated before SELECT).

SELECT
  first_name || ' ' || last_name AS full_name,
  email
FROM customer
ORDER BY full_name;

Expressions and computed columns

SELECT
  product_name,
  price,
  quantity,
  price * quantity AS total_value
FROM order_items
ORDER BY total_value DESC;

SELECT with WHERE, ORDER BY, and LIMIT

SELECT
  order_id,
  customer_id,
  total_amount,
  placed_at
FROM orders
WHERE placed_at >= now() - interval '30 days'
  AND total_amount > 50
ORDER BY placed_at DESC
LIMIT 20;

DISTINCT: removing duplicate rows

-- Unique cities customers are from
SELECT DISTINCT city FROM customer ORDER BY city;

PostgreSQL also supports DISTINCT ON — a powerful extension that returns one row per group based on a specified column:

-- Most recent order per customer
SELECT DISTINCT ON (customer_id)
  customer_id, order_id, placed_at
FROM orders
ORDER BY customer_id, placed_at DESC;

Production tips

• Always list columns explicitly in application queries — SELECT * will break silently if columns are added or reordered.
• Use column aliases to match your application's expected field names, reducing transformation code in the application layer.
• Qualify column names with table aliases (e.g. o.order_id) in multi-table queries to avoid ambiguity.
• Use EXPLAIN to check how PostgreSQL plans your query before deploying to production.

Reference: PostgreSQL documentation — SELECT.