How to Query Data in PostgreSQL — A Beginner’s Guide with Examples
Introduction
PostgreSQL (or Postgres) is one of the most powerful and open-source relational database systems used by developers, data analysts, and businesses worldwide.
If you’ve already created a database and inserted some records, the next logical step is learning how to query data — that means retrieving, filtering, sorting, and analyzing the information stored in your tables.
In this post, we’ll explore how to query data in PostgreSQL using simple and practical SQL commands that even beginners can master.
What Is a Query?
A query is a request you send to the database to get specific information.
You use SQL (Structured Query Language) commands to write these requests.
In PostgreSQL, the most common query command is:
It tells the database which columns and records you want to retrieve.Basic Query Syntax
Here’s the basic structure of a SELECT query in PostgreSQL:
SELECT column1, column2, ...
FROM table_name;Example:SELECT first_name, last_name FROM employees;
✅ This retrieves thefirst_nameandlast_namecolumns from theemployeestable.
If you want to select all columns, use the asterisk *:
SELECT * FROM employees;
🔍 Filtering Data with WHERE
To get only specific records, use the WHERE clause.
Example:
SELECT *
FROM employees
WHERE department = 'Finance';This query returns only employees who work in the Finance department.
You can also use comparison operators like:
| Operator | Description | Example |
|---|---|---|
| = | Equal to | WHERE salary = 50000 |
| <> or != | Not equal to | WHERE department <> 'HR' |
| >, <, >=, <= | Greater or less than | WHERE age > 30 |
| LIKE | Pattern matching | WHERE name LIKE 'A%' (names starting with A) |
| IN | Matches a list | WHERE department IN ('HR', 'IT', 'Finance') |
🎯 Sorting Results with ORDER BY
To arrange results in ascending or descending order:
SELECT name, salary
FROM employees
ORDER BY salary DESC;
DESC = descending (highest first)
ASC = ascending (lowest first, default)
🧮 Limiting the Number of Results
You can show only a specific number of rows using LIMIT.
Example:
SELECT *
FROM employees
LIMIT 5;
This displays only the first five rows from the result set.
🧠 Combining Conditions with AND / OR
SELECT *
FROM employees
WHERE department = 'IT' AND salary > 40000;
You can also use OR:
SELECT *
FROM employees
WHERE department = 'IT' OR department = 'Finance';
📊 Aggregate Functions for Summary Data
PostgreSQL supports powerful functions for summarizing data:
| Function | Description | Example |
|---|---|---|
| COUNT() | Counts rows | SELECT COUNT(*) FROM employees; |
| AVG() | Average value | SELECT AVG(salary) FROM employees; |
| SUM() | Total value | SELECT SUM(salary) FROM employees; |
| MAX() | Highest value | SELECT MAX(salary) FROM employees; |
| MIN() | Lowest value | SELECT MIN(salary) FROM employees; |
You can combine them with GROUP BY for summaries by category:
SELECT department, AVG(salary)
FROM employees
GROUP BY department;
🔗 Joining Tables
In real projects, data is often split into multiple tables.
To combine related data, use JOIN.
Example (joining employees and departments):
SELECT e.name, d.department_name
FROM employees e
JOIN departments d
ON e.department_id = d.id;
Types of joins:
-
INNER JOIN– only matching records -
LEFT JOIN– all from left + matches from right -
RIGHT JOIN– all from right + matches from left -
FULL JOIN– all records from both sides
🧰 Practical Example
Let’s assume we have a database with a table called students.
| id | name | age | grade | city |
|---|---|---|---|---|
| 1 | Arun | 15 | A | Kochi |
| 2 | Meera | 14 | B | Thrissur |
| 3 | Rahul | 15 | A | Ernakulam |
| 4 | Anju | 13 | C | Kollam |
Query example:
SELECT name, grade
FROM students
WHERE city = 'Ernakulam';
Output:
| name | grade |
|---|---|
| Rahul | A |
⚙️ Tools to Run PostgreSQL Queries
You can write and test these queries using:
-
pgAdmin (PostgreSQL’s official GUI)
-
psql command-line tool
-
DBeaver (open-source database client)
-
DataGrip (JetBrains IDE)
-
VS Code SQL extensions
🚀 Conclusion
Querying data in PostgreSQL is one of the most essential skills for working with databases.
With the right SELECT, WHERE, and JOIN commands, you can pull meaningful insights from your data easily.
Start simple, practice often, and soon you’ll be able to write complex queries confidently.
💡 Tip: Try combining filters, joins, and aggregates in a single query — that’s where PostgreSQL truly shines!
