This blog teaches you how to use the SELECT statement to query data from PostgreSQL tables. You will also learn how to filter and sort the results with the WHERE and ORDER BY clauses.
1. Introduction
PostgreSQL is a powerful and open-source relational database management system that supports various data types, functions, operators, and features. One of the most common and essential tasks in PostgreSQL is querying data from tables using the SELECT statement.
The SELECT statement allows you to retrieve data from one or more tables based on certain criteria. You can also use the WHERE and ORDER BY clauses to filter and sort the results according to your needs.
In this tutorial, you will learn how to use the SELECT statement to query data from PostgreSQL tables. You will also learn how to use the WHERE and ORDER BY clauses to filter and sort the results. By the end of this tutorial, you will be able to write basic and complex queries using the SELECT statement in PostgreSQL.
Before you begin, you will need to have PostgreSQL installed and running on your system. You will also need to have access to a PostgreSQL database and a table with some sample data. You can use the following commands to create a database named testdb and a table named customers with some sample data:
-- create a database named testdb
CREATE DATABASE testdb;
-- connect to the testdb database
\c testdb;
-- create a table named customers
CREATE TABLE customers (
id SERIAL PRIMARY KEY,
name VARCHAR(50) NOT NULL,
email VARCHAR(50) UNIQUE NOT NULL,
country VARCHAR(50) NOT NULL
);
-- insert some sample data into the customers table
INSERT INTO customers (name, email, country) VALUES
('Alice', 'alice@example.com', 'USA'),
('Bob', 'bob@example.com', 'Canada'),
('Charlie', 'charlie@example.com', 'UK'),
('David', 'david@example.com', 'Australia'),
('Eve', 'eve@example.com', 'France');
Now that you have a database and a table with some sample data, you are ready to start querying data from PostgreSQL tables using the SELECT statement.
2. PostgreSQL SELECT Statement Syntax
The syntax of the PostgreSQL SELECT statement is as follows:
SELECT column_list FROM table_name WHERE condition ORDER BY column_list;
The SELECT statement consists of four main clauses:
- The SELECT clause specifies the columns that you want to retrieve from the table. You can use the asterisk (*) to select all columns, or you can list the column names separated by commas.
- The FROM clause specifies the table that you want to query data from. You can use the table name or an alias for the table.
- The WHERE clause specifies the condition that the rows must satisfy to be selected. You can use various operators and expressions to construct the condition. The WHERE clause is optional, but it is often used to filter the data.
- The ORDER BY clause specifies the order in which the rows are returned. You can use one or more columns to sort the data in ascending or descending order. The ORDER BY clause is also optional, but it is often used to sort the data.
In the next sections, you will learn how to use each clause of the SELECT statement in more detail.
2.1. SELECT Clause
The SELECT clause is the first and most important clause of the SELECT statement. It determines which columns you want to retrieve from the table. You can use the asterisk (*) to select all columns, or you can list the column names separated by commas.
For example, the following query selects all columns from the customers table:
SELECT * FROM customers;
The result is a table with five columns: id, name, email, country, and phone. Each row represents a customer record.
If you want to select only specific columns from the table, you can list them after the SELECT keyword. For example, the following query selects only the name and email columns from the customers table:
SELECT name, email FROM customers;
The result is a table with two columns: name and email. Each row represents a customer’s name and email address.
When you list the column names, you can use aliases to rename them in the result. An alias is a temporary name that you assign to a column using the AS keyword. For example, the following query selects the name and email columns from the customers table and renames them as customer_name and customer_email in the result:
SELECT name AS customer_name, email AS customer_email FROM customers;
The result is a table with two columns: customer_name and customer_email. Each row represents a customer’s name and email address with the new column names.
Using aliases can help you make the result more readable and meaningful. You can also use aliases to avoid name conflicts when you join multiple tables with the same column names.
In the next section, you will learn how to use the FROM clause to specify the table that you want to query data from.
2.2. FROM Clause
The FROM clause is the second clause of the SELECT statement. It specifies the table that you want to query data from. You can use the table name or an alias for the table.
For example, the following query selects all columns from the customers table using the table name:
SELECT * FROM customers;
The result is the same as the previous query that used the asterisk (*) to select all columns.
If you want to use an alias for the table, you can use the AS keyword or simply write the alias after the table name. For example, the following query selects all columns from the customers table using the alias c:
SELECT * FROM customers AS c; -- or SELECT * FROM customers c;
The result is the same as the previous query that used the table name, but the table name is replaced by the alias in the output.
Using an alias for the table can help you simplify the query and avoid typing the full table name. You can also use an alias to join multiple tables with the same name or to join a table with itself.
In the next section, you will learn how to use the WHERE clause to filter the rows that you want to select from the table.
2.3. WHERE Clause
The WHERE clause is the third clause of the SELECT statement. It specifies the condition that the rows must satisfy to be selected from the table. You can use various operators and expressions to construct the condition. The WHERE clause is optional, but it is often used to filter the data.
For example, the following query selects all columns from the customers table where the country column is equal to ‘USA’:
SELECT * FROM customers WHERE country = 'USA';
The result is a table with five columns: id, name, email, country, and phone. Each row represents a customer record from the USA.
You can use various operators to compare the values in the WHERE clause, such as:
- = (equal to)
- <> or != (not equal to)
- < (less than)
- <= (less than or equal to)
- > (greater than)
- >= (greater than or equal to)
You can also use logical operators to combine multiple conditions, such as:
- AND (returns true if both conditions are true)
- OR (returns true if either condition is true)
- NOT (returns true if the condition is false)
For example, the following query selects all columns from the customers table where the country column is either ‘USA’ or ‘Canada’:
SELECT * FROM customers WHERE country = 'USA' OR country = 'Canada';
The result is a table with five columns: id, name, email, country, and phone. Each row represents a customer record from the USA or Canada.
In the next section, you will learn how to use the ORDER BY clause to sort the rows that you select from the table.
2.4. ORDER BY Clause
The ORDER BY clause is the fourth and final clause of the SELECT statement. It specifies the order in which the rows are returned. You can use one or more columns to sort the data in ascending or descending order. The ORDER BY clause is optional, but it is often used to sort the data.
For example, the following query selects all columns from the customers table and sorts them by the name column in ascending order:
SELECT * FROM customers ORDER BY name;
The result is a table with five columns: id, name, email, country, and phone. Each row represents a customer record sorted by the name alphabetically.
If you want to sort the data in descending order, you can use the DESC keyword after the column name. For example, the following query selects all columns from the customers table and sorts them by the name column in descending order:
SELECT * FROM customers ORDER BY name DESC;
The result is a table with five columns: id, name, email, country, and phone. Each row represents a customer record sorted by the name in reverse alphabetical order.
If you want to sort the data by multiple columns, you can list them separated by commas. For example, the following query selects all columns from the customers table and sorts them by the country column in ascending order and then by the name column in descending order:
SELECT * FROM customers ORDER BY country, name DESC;
The result is a table with five columns: id, name, email, country, and phone. Each row represents a customer record sorted by the country alphabetically and then by the name in reverse alphabetical order within each country.
Using the ORDER BY clause can help you organize the data and make it easier to analyze. You can also use the ORDER BY clause to rank the data based on some criteria.
In the next section, you will see some examples of how to use the SELECT statement to query data from PostgreSQL tables.
3. PostgreSQL SELECT Statement Examples
In this section, you will see some examples of how to use the SELECT statement to query data from PostgreSQL tables. You will use the customers table that you created in the introduction section as the sample data source. You will also use the keyphrases that are relevant to this section, such as PostgreSQL, select, where, order by, filter, and sort.
The examples will cover different scenarios and use cases of the SELECT statement, such as:
- How to select distinct values from a column
- How to use arithmetic operators and expressions in the SELECT clause
- How to use string functions and concatenation in the SELECT clause
- How to use the IN operator to check for multiple values in the WHERE clause
- How to use the BETWEEN operator to check for a range of values in the WHERE clause
- How to use the LIKE operator to perform pattern matching in the WHERE clause
- How to use the LIMIT clause to limit the number of rows returned by the SELECT statement
- How to use the OFFSET clause to skip a number of rows before returning the result
- How to use the FETCH clause to limit the number of rows returned by the SELECT statement with a more readable syntax
For each example, you will see the query, the result, and a brief explanation of how the query works. You will also see some code snippets to illustrate the syntax and the output of the query.
Let’s start with the first example: how to select distinct values from a column.
3.1. Selecting All Columns from a Table
One of the simplest ways to use the SELECT statement is to select all columns from a table. You can do this by using the asterisk (*) after the SELECT keyword. This will return a table with all the columns and rows from the original table.
For example, the following query selects all columns from the customers table that you created in the introduction section:
SELECT * FROM customers;
The result is a table with five columns: id, name, email, country, and phone. Each row represents a customer record with the corresponding values for each column.
Selecting all columns from a table can be useful when you want to see the entire data set or when you are not sure which columns you need. However, selecting all columns can also have some drawbacks, such as:
- It can return a large amount of data that may be unnecessary or irrelevant for your analysis.
- It can reduce the performance and efficiency of your query, especially if the table has many columns or rows.
- It can make the result less readable and understandable, especially if the column names are not descriptive or meaningful.
Therefore, it is often recommended to select only the columns that you need for your analysis. You can do this by listing the column names after the SELECT keyword, separated by commas. You will see how to do this in the next section.
3.2. Selecting Specific Columns from a Table
As you saw in the previous section, you can use the asterisk (*) to select all columns from a table. However, this can result in a large amount of data that may be unnecessary or irrelevant for your analysis. Therefore, it is often recommended to select only the columns that you need for your analysis. You can do this by listing the column names after the SELECT keyword, separated by commas.
For example, the following query selects only the name and country columns from the customers table:
SELECT name, country FROM customers;
The result is a table with two columns: name and country. Each row represents a customer’s name and country.
Selecting specific columns from a table can have some benefits, such as:
- It can reduce the amount of data that is returned by the query, making it more efficient and faster.
- It can make the result more readable and understandable, especially if the column names are descriptive and meaningful.
- It can help you focus on the data that is relevant and important for your analysis.
When you list the column names, you can also use aliases to rename them in the result. An alias is a temporary name that you assign to a column using the AS keyword. You will see how to use aliases in the next section.
3.3. Filtering Rows with the WHERE Clause
The WHERE clause is the third clause of the SELECT statement. It specifies the condition that the rows must satisfy to be selected. You can use various operators and expressions to construct the condition. The WHERE clause is optional, but it is often used to filter the data.
For example, the following query selects all columns from the customers table where the country column is equal to ‘USA’:
SELECT * FROM customers WHERE country = 'USA';
The result is a table with five columns: id, name, email, country, and phone. Each row represents a customer record from the USA.
You can use different operators to compare the values in the WHERE clause, such as:
- The equal operator (=) to check for equality
- The not equal operator (<>) to check for inequality
- The greater than operator (>) to check for greater values
- The less than operator (<) to check for smaller values
- The greater than or equal operator (>=) to check for greater or equal values
- The less than or equal operator (<=) to check for smaller or equal values
You can also use logical operators to combine multiple conditions in the WHERE clause, such as:
- The AND operator to check if both conditions are true
- The OR operator to check if either condition is true
- The NOT operator to negate a condition
For example, the following query selects all columns from the customers table where the country column is equal to ‘USA’ and the name column starts with ‘A’:
SELECT * FROM customers WHERE country = 'USA' AND name LIKE 'A%';
The result is a table with five columns: id, name, email, country, and phone. Each row represents a customer record from the USA whose name starts with ‘A’.
In this example, you also see how to use the LIKE operator to perform pattern matching in the WHERE clause. The LIKE operator allows you to use wildcards to match a partial or complete string. The most common wildcards are:
- The percent sign (%) to match any sequence of zero or more characters
- The underscore sign (_) to match any single character
Using the WHERE clause can help you filter the data and select only the rows that meet your criteria. You can also use the WHERE clause to perform calculations and aggregations on the filtered data.
In the next section, you will learn how to use the ORDER BY clause to sort the rows by one or more columns.
3.4. Sorting Rows with the ORDER BY Clause
The ORDER BY clause is the fourth and last clause of the SELECT statement. It specifies the order in which the rows are returned. You can use one or more columns to sort the data in ascending or descending order. The ORDER BY clause is optional, but it is often used to sort the data.
For example, the following query selects all columns from the customers table and sorts them by the name column in ascending order:
SELECT * FROM customers ORDER BY name;
The result is a table with five columns: id, name, email, country, and phone. Each row represents a customer record sorted by the name alphabetically.
You can use the ASC keyword to specify the ascending order, or the DESC keyword to specify the descending order. If you omit the ASC or DESC keyword, the default order is ascending. For example, the following query selects all columns from the customers table and sorts them by the name column in descending order:
SELECT * FROM customers ORDER BY name DESC;
The result is a table with five columns: id, name, email, country, and phone. Each row represents a customer record sorted by the name in reverse alphabetical order.
You can also use multiple columns to sort the data by more than one criterion. For example, the following query selects all columns from the customers table and sorts them by the country column in ascending order, and then by the name column in descending order:
SELECT * FROM customers ORDER BY country, name DESC;
The result is a table with five columns: id, name, email, country, and phone. Each row represents a customer record sorted by the country alphabetically, and then by the name in reverse alphabetical order within each country.
Using the ORDER BY clause can help you sort the data and present it in a more meaningful and organized way. You can also use the ORDER BY clause to perform calculations and aggregations on the sorted data.
In the next section, you will learn how to use the LIMIT, OFFSET, and FETCH clauses to limit the number of rows returned by the SELECT statement.
4. Conclusion
In this tutorial, you learned how to use the SELECT statement to query data from PostgreSQL tables. You also learned how to use the WHERE and ORDER BY clauses to filter and sort the results according to your needs.
Here are some key points to remember:
- The SELECT clause specifies the columns that you want to retrieve from the table. You can use the asterisk (*) to select all columns, or you can list the column names separated by commas.
- The FROM clause specifies the table that you want to query data from. You can use the table name or an alias for the table.
- The WHERE clause specifies the condition that the rows must satisfy to be selected. You can use various operators and expressions to construct the condition. The WHERE clause is optional, but it is often used to filter the data.
- The ORDER BY clause specifies the order in which the rows are returned. You can use one or more columns to sort the data in ascending or descending order. The ORDER BY clause is also optional, but it is often used to sort the data.
By using the SELECT statement, you can perform various data analysis tasks on PostgreSQL tables. You can also use other clauses and functions to perform more advanced queries, such as calculations, aggregations, joins, subqueries, and more.
We hope you enjoyed this tutorial and found it useful. If you have any questions or feedback, please let us know in the comments below. Happy querying!
