PostgreSQL SELECT Statement

The SELECT statement is the most frequently used SQL command. It retrieves data from one or more tables and returns it as a result set. Every time data needs to be read from a database — whether to display it on a screen, analyze it, or pass it to an application — SELECT is the tool.

Basic Syntax of SELECT

SELECT column1, column2 FROM table_name;

This tells PostgreSQL: "Go to table_name and return the values in column1 and column2." The result is a virtual table made of those columns and all matching rows.

Selecting All Columns

To retrieve every column from a table, use the asterisk * wildcard:

SELECT * FROM students;

This returns all rows and all columns from the students table. While convenient during exploration, listing specific column names is better practice in production code because it makes queries predictable even if the table structure changes later.

Selecting Specific Columns

To retrieve only certain columns:

SELECT name, age FROM students;

Only the name and age columns will appear in the result, even though the table may have more columns.

Using Column Aliases

An alias gives a column a temporary display name in the result. Aliases do not change the actual column name in the table.

SELECT name AS student_name, age AS student_age
FROM students;

The output will show column headers student_name and student_age instead of name and age. This is useful when column names are technical or when the same column is used multiple times with different calculations.

Performing Calculations in SELECT

SELECT can do arithmetic directly. For example, if a products table has price and tax_rate columns:

SELECT
    name,
    price,
    price * tax_rate AS tax_amount,
    price + (price * tax_rate) AS total_price
FROM products;

The calculated columns tax_amount and total_price appear in the result without being stored in the table.

Selecting Distinct Values

The DISTINCT keyword removes duplicate rows from the result:

SELECT DISTINCT city FROM customers;

If the same city appears for multiple customers, it will only appear once in the result. This is useful for finding unique values in a column.

Combining Text with CONCAT

Text values from multiple columns can be combined using CONCAT or the || operator:

SELECT first_name || ' ' || last_name AS full_name
FROM employees;

This combines first and last names with a space between them and displays the result under the alias full_name.

Selecting Literal Values

A SELECT statement can return fixed values without querying any table:

SELECT 100 + 50 AS result;
SELECT 'Hello, PostgreSQL!' AS message;
SELECT NOW() AS current_time;

These are useful for testing expressions or checking built-in functions. NOW() returns the current date and time.

Limiting Results with LIMIT

To restrict the number of rows returned, use the LIMIT clause:

SELECT name, age FROM students LIMIT 5;

This returns only the first five rows. LIMIT is explored in full in a dedicated topic, but it is introduced here because it is often paired with SELECT during data exploration.

Using SELECT with Expressions and Functions

String Functions

SELECT UPPER(name) AS uppercase_name FROM students;
SELECT LENGTH(name) AS name_length FROM students;

UPPER() converts text to uppercase. LENGTH() returns the number of characters in a string.

Mathematical Functions

SELECT ROUND(price, 2) AS rounded_price FROM products;

ROUND() rounds a number to the specified number of decimal places.

The ORDER of Clauses in a SELECT Statement

A full SELECT statement can include several clauses. They must always appear in this order:

SELECT columns
FROM table
WHERE condition
GROUP BY columns
HAVING condition
ORDER BY columns
LIMIT number;

Not all clauses are required. The only mandatory parts are SELECT and FROM. The other clauses are covered in upcoming topics.

Example: Complete SELECT Queries

-- Create and populate a table
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department VARCHAR(50),
    salary NUMERIC(10, 2)
);

INSERT INTO employees (first_name, last_name, department, salary)
VALUES
    ('Anna', 'Smith', 'HR', 55000),
    ('Ben', 'Jones', 'IT', 72000),
    ('Clara', 'Lee', 'IT', 68000),
    ('Dan', 'Brown', 'Finance', 81000);

-- Select all columns
SELECT * FROM employees;

-- Select specific columns
SELECT first_name, last_name, salary FROM employees;

-- Use alias and combine columns
SELECT first_name || ' ' || last_name AS full_name,
       salary AS annual_salary
FROM employees;

-- Select distinct departments
SELECT DISTINCT department FROM employees;

-- Calculate a 10% salary bonus
SELECT first_name, salary, salary * 0.10 AS bonus FROM employees;

Key Points

• SELECT retrieves data from one or more tables.
• Use * to select all columns or list specific column names for precise queries.
• Aliases created with AS rename columns in the output without affecting the table.
• SELECT can perform calculations and call functions as part of the query.
• DISTINCT removes duplicate rows from the result.
• The order of SQL clauses (WHERE, GROUP BY, ORDER BY) must follow a fixed sequence.