PostgreSQL SELECT: choosing columns

SELECT is the command you will write most. It reads data and never changes it. You list the columns you want, separated by commas, and PostgreSQL returns them in that order.

A select list can hold more than plain column names: arithmetic (price * stock), text joining with ||, function calls, and constants. Give computed columns readable names with AS. If a name contains spaces or capitals, wrap it in double quotes.

Prefer naming columns over SELECT * in real code: it documents intent, moves less data, and doesn’t break when someone adds a column.

Syntax

SELECT column1, column2 AS alias, expression AS name
FROM table_name;

Examples

Edit the code and run it. Output is printed the way psql prints it.

3.1Pick specific columns

1
2

SELECT name, category, price
FROM products;

Only three of the eight columns come back, in the order listed. Row order is not guaranteed without ORDER BY (covered soon).

3.2Calculated columns and aliases

SELECT name,
       price,
       stock,
       price * stock AS inventory_value,
       price * 0.9   AS "Sale Price"
FROM products;

price * stock is computed for every row.
AS inventory_value names the result.
"Sale Price" needs double quotes because of the space and capitals.

3.3Joining text together

SELECT first_name || ' ' || last_name AS full_name,
       title,
       'Works since ' || hire_date AS note
FROM employees;

|| concatenates text. PostgreSQL converts hire_date to text automatically when it is concatenated with a string. Note: if any part is NULL, || returns NULL; concat() skips NULLs instead.

3.4SELECT without a table

SELECT 7 * 6 AS answer, upper('postgres') AS shout, 10 / 4 AS int_div, 10 / 4.0 AS real_div;

Handy as a calculator. Notice 10 / 4 gives 2: dividing two integers produces an integer. Make one side decimal (4.0) to get 2.5.

Exercises

Write a query and check it. Any query that returns the right rows passes.

Exercise 3.1

For every employee show full_name (first and last name with a space) and monthly_salary (salary divided by 12, rounded to 2 decimals).

Hint

ROUND(value, 2) rounds a numeric value.

Solution

SELECT first_name || ' ' || last_name AS full_name, ROUND(salary / 12, 2) AS monthly_salary FROM employees;

Exercise 3.2

Show each product’s name and its price including 18% tax as price_with_tax, rounded to 2 decimals.

Hint

Multiply by 1.18.

Solution

SELECT name, ROUND(price * 1.18, 2) AS price_with_tax FROM products;