PostgreSQL SELECT - PostgreSQL Tutorial

SQL

SELECT * FROM books;

SELECT title, price FROM books;

SELECT title AS book_title, price AS cost FROM books;

SELECT * is fine when exploring, but name your columns in application code — otherwise adding a column later silently changes what your code receives.

Expressions

SQL

SELECT
    title,
    price,
    price * 1.13 AS price_with_vat,
    upper(title) AS shouty
FROM books;

DISTINCT

SQL

SELECT DISTINCT author FROM books;
SELECT DISTINCT author, published FROM books;   -- distinct combinations

Sorting

SQL

SELECT title, price FROM books ORDER BY price DESC;

SELECT * FROM books ORDER BY author ASC, price DESC;

-- NULLs sort last by default with DESC, first with ASC — be explicit:
SELECT * FROM books ORDER BY published DESC NULLS LAST;

Limiting

SQL

SELECT * FROM books ORDER BY price DESC LIMIT 10;

SELECT * FROM books ORDER BY id LIMIT 10 OFFSET 20;   -- page 3

Always pair LIMIT with ORDER BY. Without an explicit order, the database may return rows in any order, and "the top 10" becomes meaningless.

Note that large OFFSET values get slow — the database still has to walk past all those rows. For deep pagination, use keyset pagination instead:

SQL

SELECT * FROM books WHERE id > 1000 ORDER BY id LIMIT 10;

Concatenation and NULL

SQL

SELECT title || ' by ' || author AS label FROM books;

Careful: if author is NULL, the whole result is NULL. Guard it:

SQL

SELECT title || ' by ' || COALESCE(author, 'Unknown') AS label FROM books;

COALESCE returns the first non-NULL argument — one of the most useful functions in everyday SQL.