Summary: in this tutorial, you are going to learn how to use basic PostgreSQL SELECT statement to query data from a table.
One of the most common tasks, when you work with PostgreSQL, is to query data from tables by using the
SELECT statement. The
SELECT statement is one of the most complex statements in PostgreSQL. It has many clauses that you can combine to form a powerful query.
Because of its complexity, we divide the PostgreSQL
SELECT statement tutorial into many shorter tutorials so that you can learn each clause of the
SELECT statement easier. The following are the clauses that appear in the
- Select distinct rows by using
- Filter rows by using
- Sort rows by using
- Select rows based on various operators such as
- Group rows into groups using
- Apply conditions for groups using
- Join a table to other tables using
FULL OUTER JOIN,
In this tutorial, you are going to focus on the
SELECT statement syntax
Let’s start with a basic form of the
SELECT statement that retrieves data from a single table.
The following illustrates the syntax of the
Let’s examine the
SELECTstatement in more detail:
- First, you specify the column of the table from which you want to query data in the
SELECTclause. If you retrieve data from multiple columns, you use a comma to separate two columns. In case you want to query data from all columns, you can use an asterisk (*) as the shorthand.
- Second, you indicate the table name after the
selecthas the same effect. By convention, we will use SQL keywords in uppercase to make the code easier to read.
Let’s take a look at some examples of using PostgreSQL
SELECT statement. We will use the following
customers table in the sample database for the demonstration.
To query data from all rows and all columns of the
customer table, you use the following query:
Notice that we have added a semicolon (;) at the end of the
SELECT statement. The semicolon is not a part of the SQL statement. It is only for PostgreSQL to specify the end of an SQL statement.
It is not a good practice to use the asterisk (*) in the
SELECT statement. Imagine that you have a big table with many columns, the
SELECT statement with an asterisk (*) will retrieve all the data from the entire columns, which may not be necessary. In addition, retrieving unnecessary data from a table increases the traffic between the Database server and Applications. As the result, your application will be slow and less scalable. Therefore, it is a good practice to specify the column names explicitly in the
SELECT clause whenever possible to get only needed data from a table.
Suppose you just want to know the first name, last name and email of customers, you can specify the column names in the
SELECT statement as follows:
In this tutorial, you have learned how to use a basic form of PostgreSQL
SELECT statement to query data from a database table.