SELECT: asking for columns

SELECT is the statement you will write more than all the others put together. This lesson is its shape, and the two habits worth forming on the first day.

The data

Every lesson from here runs against the Kothrud Community Library — 40 books, 20 authors, 28 members, 180 loans. Load it once:

psql -U postgres -d library -f schema.sql
psql -U postgres -d library -f seed.sql

Both files are in the course repository. Your output should match the lesson's exactly, which is the point — if it does not, something is different and it is worth finding out what.

Look at the shape first:

\d books

                          Table "public.books"
   Column    |  Type   | Nullable |           Default
-------------+---------+----------+------------------------------
 id          | bigint  | not null | generated always as identity
 title       | text    | not null |
 author_id   | bigint  | not null |
 published   | date    |          |
 isbn        | text    |          |
 copies      | integer | not null | 1
 price_paise | integer |          |
 shelf       | text    |          |

Money is price_paise, an integer count of paise. Never a float. That is the rule from every other course here and it is the rule in databases too — module 4 explains what happens if you ignore it.

The shape of a SELECT

SELECT   title, published        -- which columns
FROM     books                   -- from which table
WHERE    copies > 2              -- which rows
ORDER BY published               -- in what order
LIMIT    5;                      -- how many

Written in that order, and executed in a different one — roughly FROM, then WHERE, then SELECT, then ORDER BY, then LIMIT. That mismatch explains several things that otherwise look arbitrary, including why you usually cannot use a column alias in WHERE.

Only SELECT is compulsory. Everything else is optional.

Asking for columns

SELECT title, published FROM books LIMIT 5;

       title       | published
-------------------+------------
 Malgudi Days      | 1943-01-01
 The Guide         | 1958-01-01
 Swami and Friends | 1935-01-01
 Train to Pakistan | 1956-01-01
 Delhi             | 1990-01-01
(5 rows)

The order you list them is the order you get them. SELECT published, title gives the same rows with the columns swapped.

`SELECT *`, and why not to ship it

SELECT * FROM books LIMIT 5;

* means every column. It is excellent while exploring and a bad habit in code that ships, for three reasons:

It fetches data you do not need. Every byte crosses the network. On a table with a long description column you are moving kilobytes per row to display a title.

It breaks when the table changes. Somebody adds a column and your code receives a shape it did not expect. Worse, somebody reorders columns and positional access silently reads the wrong one.

It hides what the query needs. SELECT id, title FROM books tells a reader — and you, in six months — exactly what this code depends on.

Use * at the prompt. Name your columns in code.

Expressions and aliases

Columns do not have to exist. You can compute them:

SELECT title, price_paise / 100.0 AS price_rupees FROM books LIMIT 4;

       title       |     price_rupees
-------------------+----------------------
 Malgudi Days      | 295.0000000000000000
 The Guide         | 325.0000000000000000
 Swami and Friends | 275.0000000000000000
 Train to Pakistan | 349.0000000000000000
(4 rows)

Two things in that output are worth stopping on.

AS price_rupees is the alias. Without it the column would be called ?column?, which is PostgreSQL telling you it has no idea what to call the result of an arithmetic expression. AS is optional — price_paise / 100.0 price_rupees works — and write it anyway, because without it a missing comma turns a column into an alias silently:

SELECT title price_paise FROM books;   -- one column called price_paise. Not an error.

That is a genuinely nasty typo and AS makes it visible.

Eighteen decimal places. Dividing an integer by 100.0 — a numeric literal — produces numeric, which PostgreSQL prints at full precision. Module 4 covers types properly; for now, ROUND(price_paise / 100.0, 2) if you want two.

Text, and the two-pipe operator

SELECT title || ' (' || shelf || ')' AS shelved FROM books LIMIT 3;

         shelved
--------------------------
 Malgudi Days (F-01)
 The Guide (F-01)
 Swami and Friends (F-01)

|| is concatenation in standard SQL — not +, which is arithmetic. If any operand is NULL the whole result is NULL, which catches people constantly and has a lesson of its own two lessons from here.

Single quotes are for strings. Always.

SELECT 'Malgudi Days';      -- a string
SELECT "Malgudi Days";      -- ERROR: column "Malgudi Days" does not exist

Double quotes mean identifier — a table or column name. This is the opposite of most programming languages and it catches everybody once.

An apostrophe inside a string is doubled:

SELECT 'Narayan''s Malgudi';

You will rarely type that yourself, because putting user input into SQL by hand is how SQL injection happens. Module 9 is about the one habit that prevents it.

Reading the output

(5 rows)

psql tells you how many rows came back. Get into the habit of reading it — a query returning far more or far fewer rows than you expected is the cheapest bug signal there is.

And two commands from the install lesson that pay off immediately:

\x          -- expanded output: one column per line. For wide rows.
\timing     -- show how long each query took

\x turns an unreadable 8-column row into a readable list. \timing is how you will measure everything in module 7 — turn it on now and get used to seeing the numbers.

`DISTINCT`, briefly

SELECT DISTINCT shelf FROM books ORDER BY shelf;

Removes duplicate rows from the result. It is useful and it is also frequently a sign that the query is wrong — a join producing duplicates you then remove is a join you should look at. Module 3 covers counting things correctly.

No table at all

SELECT 2 + 2 AS answer;
SELECT current_date, current_timestamp;
SELECT version();

FROM is optional. This is the fastest way to test what an expression does without involving your data, and it is how the next lessons demonstrate NULL behaviour.

Check your work

The order clauses are written in. SELECT, FROM, WHERE, ORDER BY, LIMIT — and they execute in a different order.

Which clause is compulsory. Only SELECT.

Three reasons not to ship SELECT *. Fetches what you do not need, breaks when the table changes, and hides what the query depends on.

What ?column? means. You computed something and did not name it.

Why write AS even though it is optional. A missing comma otherwise turns a column into an alias silently.

Why price_rupees had eighteen decimal places. integer / numeric gives numeric, printed at full precision.

The concatenation operator. ||, not + — and NULL anywhere makes the whole result NULL.

Single versus double quotes. Single for strings, double for identifiers. The opposite of most languages.

The cheapest bug signal. The (n rows) count not being what you expected.

When DISTINCT is a smell. When it is hiding duplicates a join created.

Practice

Load the schema and seed files. Confirm SELECT count(*) FROM books; returns 40.
Run \d on all six tables and write down which columns can be NULL.
Select just title from books. Then title, shelf. Then swap the order.
Run SELECT * on loans and then name only the three columns you would actually use.
Compute the price in rupees, rounded to two decimal places.
Write a query with a missing comma between two columns. Explain the result.
Build a single text column reading Malgudi Days by author 1 — shelf F-01.
Concatenate a book's title with its isbn, then with a column that is NULL for some rows. Note what happens.
Run SELECT "title" FROM books; and then SELECT 'title' FROM books;. Explain both.
Select 2 + 2, current_date and version() with no FROM.
Turn on \x and select one whole row from members. Turn it off.
Turn on \timing and run a query twice. Note that the second is faster, and guess why.
Get the list of distinct shelf values, and the count of them.

Official documentation

PostgreSQL — Queries — The whole SELECT statement, clause by clause.
PostgreSQL — SELECT reference — The complete syntax, including the evaluation order that explains the alias rules.
PostgreSQL — Lexical structure — Why single quotes are strings and double quotes are identifiers.
PostgreSQL — String functions and operators — ||, concat(), and the difference when NULL is involved.

Next: filtering rows.