Postgres by Example: SELECT Basics

SELECT is the heart of SQL — it is how you read data. The simplest form is SELECT expression, with no FROM, which evaluates the expression and returns one row. The more common form, SELECT columns FROM table, returns one row per matching row in the table. Either way, the result is a relation: rows and columns, with column names you can rename using AS.

What you'll learn:

• SELECT with literals and expressions (no table needed)
• Naming result columns with AS
• Selecting multiple columns in one query
• Reading from a system catalog table
• Column aliases vs. table aliases

-- Literals and expressions: no FROM required
SELECT 1 AS one;
SELECT 2 + 3 AS sum, 10 / 4 AS quotient, 10 / 4.0 AS real_quotient;
SELECT 'hello' AS greeting, length('hello') AS chars;

-- Multiple columns in one row
SELECT 1 AS a, 2 AS b, 'x' AS c;

-- Reading from a table (here, a system catalog)
SELECT relname, relkind
FROM pg_class
WHERE relname = 'pg_database'
LIMIT 1;

Each SELECT produces a result set. AS column_name renames the output column — without it, PostgreSQL invents a name like ?column? for literals or uses the source column name. The arithmetic line is worth a second look: 10 / 4 returns the integer 2 because both operands are integers, while 10 / 4.0 returns 2.5 because one operand is numeric and integer division does not apply. This trap shows up often when computing averages and ratios.

pg_class is the system catalog that lists relations — tables, indexes, views, sequences, materialized views, and more. The relkind column distinguishes them (r = ordinary table, i = index, v = view, m = materialized view, S = sequence). Looking at catalogs is a habit worth developing; everything about your schema is visible there.

To run:

$ psql -f source/select-basics.sql postgres
 one
-----
   1
(1 row)

 sum | quotient | real_quotient
-----+----------+---------------
   5 |        2 |            2.5
(1 row)

 greeting | chars
----------+-------
 hello    |     5
(1 row)

 a | b | c
---+---+---
 1 | 2 | x
(1 row)

   relname   | relkind
-------------+---------
 pg_database | r
(1 row)

Common pitfalls:

• Forgetting AS on an expression: SELECT count(*) works, but SELECT count(*) FROM ... WHERE total > 0 GROUP BY id gets unreadable without AS order_count. Always alias derived columns.
• Integer division: 1 / 2 returns 0. Cast one side: 1::numeric / 2, or 1.0 / 2.
• Double-quotes vs. single-quotes: 'hello' is a string literal; "hello" is an identifier (a column or table name). Mixing them up is one of the most common SQL beginner errors.

Tip: You can mix kinds of columns freely: SELECT id, name, id * 2 AS doubled, current_timestamp AS fetched_at FROM fruits; is perfectly fine. The result columns are evaluated in the order written and named by the alias.

Try it: Run SELECT 10 * 2 AS twenty;. Then try SELECT relname FROM pg_class WHERE relkind = 'r' LIMIT 5; to see five table names in your database. Add , relkind after relname and observe.

Source: select-basics.sql

Next: WHERE

Home: Postgres by Example