Querying data using the SELECT statement in Postgres

When dealing with databases, a frequent activity involves retrieving data from tables through the use of the SELECT statement.

In Postgres, the SELECT statement stands out as one of the more used commands, offering a range of clauses that enable the creation of versatile queries.

The SELECT statement in Postgres has several clauses that provide flexibility and control over the query results. Here are some key clauses commonly used with the SELECT statement:

Suppose that we have a table Persons in the database with the following structure and data.

FROM Clause

Specifies the table from which to retrieve data.

SELECT name, age FROM Persons;

WHERE Clause

Filters rows based on a specified condition.

SELECT * FROM Persons WHERE name = 'Eduard';

GROUP BY Clause

Groups rows based on specified columns, often used with aggregate functions.

SELECT city, COUNT(*) AS total_persons FROM Persons GROUP BY city;

HAVING Clause

Filters grouped rows based on a condition.

SELECT city, COUNT(*) FROM Persons GROUP BY city HAVING COUNT(*) > 1;

ORDER BY Clause

Sorts the result set based on specified columns.

SELECT name, age FROM Persons ORDER BY age ASC;

LIMIT Clause

Limits the number of rows returned by the query.

SELECT * FROM Persons LIMIT 3;

OFFSET Clause

Skips a specified number of rows before starting to return rows.

SELECT * FROM Persons OFFSET 3;

DISTINCT Clause

Removes duplicate rows from the result set.

SELECT DISTINCT city FROM Persons;

These clauses can be combined and used together to create powerful and customized queries in Postgres.