How to Select Data in PostgreSQL: A Beginner's Guide

If you searched for "how to select data in PostgreSQL," here is the short answer: you use a SELECT statement. It looks like SELECT column_name FROM table_name;, and it tells PostgreSQL which columns you want and which table to get them from. In this guide, you will learn exactly how SELECT works, one small step at a time, with copy-pasteable examples you can run yourself.

This is a true beginner guide. We assume you have never written SQL before. If you have not created a table yet, it may help to read our guide on creating a table in PostgreSQL and inserting data first, but you can also follow along here using the examples as-is.

What Is a SELECT Query?

A database table stores data in rows and columns, similar to a spreadsheet. A SELECT query is a command you type into PostgreSQL that asks it to read some of that data and show it back to you. SELECT does not change anything in the table. It only reads and displays data, which makes it a very safe command to practice with.

Think of it like asking a librarian a question: "Show me the titles and authors of every book published after 2020." SELECT is how you ask that question in a language PostgreSQL understands.

The Basic SELECT Syntax

Imagine a table named customers with columns for id, first_name, email, country, and signup_date. The simplest possible query looks like this:

SELECT * FROM customers;

Here is what each part means. SELECT is the keyword that starts every read query. The asterisk (*) is a shortcut that means "all columns." FROM customers tells PostgreSQL which table to read from. The semicolon (;) at the end tells PostgreSQL the command is finished. Run this, and PostgreSQL returns every row and every column in the customers table.

Selecting Only the Columns You Need

Using * is fine for exploring a small table, but in real projects you usually only want specific columns. You can list them by name, separated by commas:

SELECT first_name, email FROM customers;

This query returns only the first_name and email columns for every row, instead of every column. Being specific like this makes your results easier to read and, on large tables, faster to run because PostgreSQL has less data to send back.

Filtering Rows with WHERE

So far, every example returns all rows in the table. Usually you only want rows that match a certain condition. That is what the WHERE clause is for:

SELECT first_name, email
FROM customers
WHERE country = 'Canada';

WHERE country = 'Canada' tells PostgreSQL to only include rows where the country column is exactly 'Canada'. Notice the text value is wrapped in single quotes; PostgreSQL requires that for text, but not for numbers. You can also use other comparisons such as > (greater than), < (less than), != (not equal to), and AND / OR to combine multiple conditions.

Sorting Results with ORDER BY

By default, PostgreSQL does not guarantee any particular order for the rows it returns. To control the order, add ORDER BY:

SELECT first_name, email
FROM customers
ORDER BY first_name ASC;

ORDER BY first_name ASC sorts the results alphabetically by first name, from A to Z. ASC means "ascending" and is actually the default, so you could leave it out. To sort from Z to A, or from newest to oldest for a date column, use DESC ("descending") instead.

Limiting How Many Rows You Get Back

Some tables have millions of rows. Often you just want to peek at a few of them, like the most recent sign-ups. LIMIT does that:

SELECT first_name, email, signup_date
FROM customers
ORDER BY signup_date DESC
LIMIT 5;

This query sorts customers by signup_date with the newest first (DESC), then LIMIT 5 tells PostgreSQL to only return the first 5 rows of that sorted result. This pattern, ORDER BY plus LIMIT, is one of the most common ways beginners and experts alike explore a table quickly.

Counting Rows with COUNT()

Sometimes you do not need the actual data, just a number, such as "how many customers do we have from Canada?" For that, use the COUNT() function:

SELECT COUNT(*) FROM customers WHERE country = 'Canada';

COUNT(*) counts the number of rows that match the query, instead of returning the rows themselves. Combined with WHERE, this is a fast way to answer simple questions about your data without scrolling through a long list.

Putting It All Together

You can combine everything you have learned into one query. Here is an example that finds the 3 most recently signed-up customers from Canada:

SELECT first_name, email, signup_date
FROM customers
WHERE country = 'Canada'
ORDER BY signup_date DESC
LIMIT 3;

Reading it top to bottom: pick the first_name, email, and signup_date columns, from the customers table, but only rows where country is Canada, sorted with the newest signup first, and only show the top 3 results. This order (SELECT, FROM, WHERE, ORDER BY, LIMIT) is exactly the order PostgreSQL expects these clauses to appear in.

Common Mistakes Beginners Make

• Forgetting the semicolon (;) at the end of a query, which can make psql wait for more input.
• Using double quotes instead of single quotes around text values, like "Canada" instead of 'Canada'. In PostgreSQL, single quotes are for text values and double quotes are for column or table names.
• Forgetting that text comparisons are case-sensitive by default, so 'canada' will not match 'Canada'.
• Writing clauses in the wrong order, such as putting ORDER BY before WHERE. Remember: SELECT, FROM, WHERE, ORDER BY, LIMIT.
• Running SELECT * on a huge table just to check one column, which is slower than naming the column you actually need.

Where to Practice

The best way to learn SELECT is to run these queries yourself against real data. If you have not created a table yet, our guide on creating a table in PostgreSQL walks you through it, and our guide on inserting data shows you how to add rows you can then practice selecting.

SELECT is the foundation of nearly everything else you will do in SQL, from simple reports to complex joins across multiple tables. Once you are comfortable with SELECT, WHERE, ORDER BY, and LIMIT, you already have the tools to answer most everyday questions about your data.