PostgreSQL SELECT Statement

The SELECT statement is the most frequently used SQL command in PostgreSQL. It retrieves rows from tables, views, or expressions, and can combine data from multiple sources using joins, filter rows with conditions, sort output, remove duplicates, and compute derived values — all in a single statement.

Syntax

The minimum SELECT statement needs only a select list and a FROM clause:

SELECT column1, column2
FROM table_name;

The full clause order is:

SELECT [DISTINCT] select_list
FROM table_name
[WHERE condition]
[GROUP BY group_expression]
[HAVING group_condition]
[ORDER BY sort_expression [ASC | DESC]]
[LIMIT count]
[OFFSET skip];

To retrieve every column, use *:

SELECT * FROM table_name;

SELECT * is convenient for exploration but avoid it in application code — it returns columns in schema-defined order, which can change.

Practical Example

Create a small e-commerce dataset and explore it with SELECT:

CREATE TABLE product_categories (
  id    SERIAL PRIMARY KEY,
  name  VARCHAR(100) NOT NULL
);

CREATE TABLE products (
  id          SERIAL PRIMARY KEY,
  sku         VARCHAR(50) NOT NULL UNIQUE,
  name        VARCHAR(200) NOT NULL,
  unit_price  NUMERIC(10, 2) NOT NULL,
  stock_qty   INT NOT NULL DEFAULT 0,
  category_id INT REFERENCES product_categories(id),
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

INSERT INTO product_categories (name) VALUES ('Electronics'), ('Books'), ('Apparel');

INSERT INTO products (sku, name, unit_price, stock_qty, category_id) VALUES
  ('ELEC-001', 'Wireless Headphones',  89.99, 150, 1),
  ('ELEC-002', 'USB-C Hub',            34.99,  80, 1),
  ('BOOK-001', 'PostgreSQL Internals', 49.99, 200, 2),
  ('APRL-001', 'Logo T-Shirt',         19.99, 500, 3),
  ('APRL-002', 'Hoodie',               59.99,  60, 3);

Select specific columns:

SELECT sku, name, unit_price
FROM products;

Compute a derived column:

SELECT
  sku,
  name,
  unit_price,
  stock_qty,
  unit_price * stock_qty AS inventory_value
FROM products
ORDER BY inventory_value DESC;

Filter and sort:

SELECT sku, name, unit_price
FROM products
WHERE unit_price < 50.00
ORDER BY unit_price ASC;

Limit results and paginate:

-- First page
SELECT sku, name, unit_price
FROM products
ORDER BY created_at DESC
LIMIT 3 OFFSET 0;

-- Second page
SELECT sku, name, unit_price
FROM products
ORDER BY created_at DESC
LIMIT 3 OFFSET 3;

Inspect the table structure:

\d+ products

Query Execution Order

Understanding PostgreSQL’s logical evaluation order prevents common errors:

Because WHERE runs before SELECT, column aliases defined in the select list are not available in WHERE. They are available in ORDER BY, which runs after SELECT.

-- This works: alias used in ORDER BY (evaluated after SELECT)
SELECT name, unit_price * stock_qty AS inventory_value
FROM products
ORDER BY inventory_value DESC;

-- This fails: alias used in WHERE (evaluated before SELECT)
-- WHERE inventory_value > 5000;  -- ERROR: column does not exist

Testing with Vela

Before deploying queries or schema changes that power reporting or APIs, use Vela’s database branching to benchmark on a production-like copy without risk. Create a branch from your production database (an instant copy-on-write clone), run your SELECT queries against it, and verify row counts, execution plans with EXPLAIN ANALYZE, and application behavior — all before any change touches live data. This is especially useful when adding computed columns or refactoring complex JOIN + ORDER BY queries where performance can surprise you.

Production Tips

• List columns explicitly in application queries rather than using SELECT * — schema additions will not silently break or bloat your result sets.
• Qualify column names with table aliases (p.sku, c.name) in multi-table queries to eliminate ambiguity and make the query readable at a glance.
• Always pair LIMIT or OFFSET with ORDER BY — without a defined sort order, the rows you receive across pages are unpredictable.
• Use EXPLAIN to inspect query plans before deploying new queries; look for sequential scans on large tables that could be replaced by index scans.
• For paginating large result sets, prefer keyset pagination (comparing against the last seen value of the sort column) over OFFSET — large offsets require scanning and discarding all preceding rows.
• Avoid SELECT * in views — if the underlying table schema changes, view consumers receive unexpected columns.