06-select-from-table.md

Postgres by Example: SELECT from a Table

Now that fruits has rows, you can read them. SELECT * returns every column; listing column names returns just those, in the order you list them. The result is unordered unless you add ORDER BY — even if it looks ordered today, it may not be tomorrow.

What you'll learn:

• SELECT * FROM table for all columns
• Selecting specific columns and controlling their order in the result
• Combining SELECT with WHERE and ORDER BY
• Why SELECT * is fine for ad-hoc queries but risky in application code
• Result column names and how to override them

-- All columns. The order matches CREATE TABLE.
SELECT * FROM fruits ORDER BY id;

-- Specific columns, in the order you list them
SELECT name, id FROM fruits ORDER BY id;

-- Renaming output columns
SELECT id AS fruit_id, name AS fruit_name FROM fruits ORDER BY id LIMIT 3;

-- Combining a filter and an order
SELECT id, name
FROM fruits
WHERE id BETWEEN 2 AND 4
ORDER BY name;

-- Counting rows
SELECT count(*) AS total FROM fruits;

Run this after create-table.sql and insert.sql. SELECT * returns every column in the physical column order. Listing columns gives you total control over the result shape, which matters when the data flows into application code that maps columns by position. AS renames a column in the output without changing anything in the table.

The space character is optional around * and around operators; the SQL parser is generous with whitespace. Multi-line queries are normal and encouraged for anything beyond two columns.

To run:

$ psql -f source/select-from-table.sql postgres
 id  |   name
-----+-----------
   1 | apple
   2 | banana
   3 | cherry
   4 | date
   5 | elderberry
 101 | fruit_1
 102 | fruit_2
 103 | fruit_3
(8 rows)

    name    | id
------------+-----
 apple      |   1
 banana     |   2
 ...

Common pitfalls:

• SELECT * FROM big_table; will fetch every column of every row across the network — slow and wasteful. List the columns you actually need.
• Output column names are not guaranteed unique. SELECT id, id FROM fruits has two columns both named id — application drivers may not handle that gracefully.
• In application code, prefer explicit columns over SELECT *. When the schema adds a column, the shape of SELECT * changes and may break code that assumes a particular column order or count.

Tip: When you're exploring data, \x in psql toggles expanded display — each row prints one column per line. It is much easier to read a wide row that way.

Try it: Run SELECT * FROM fruits WHERE id > 100; to see only the generated rows. Then run SELECT name FROM fruits ORDER BY name; for alphabetical names. Then try SELECT id || ': ' || name AS label FROM fruits; to build a label column with the || string concatenation operator.

Source: select-from-table.sql

Next: LIMIT and OFFSET

Home: Postgres by Example