PostgreSQL SELECT

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 SELECTstatement:

• Select distinct rows by using DISTINCT operator.
• Filter rows by using WHERE clause.
• Sort rows by usingORDER BY clause.
• Select rows based on various operators such as BETWEEN, INand LIKE.
• Group rows into groups using GROUP BY clause
• Apply conditions for groups using HAVING clause.
• Join a table to other tables using INNER JOIN, LEFT JOIN, FULL OUTER JOIN, CROSS JOIN clauses.

In this tutorial, you are going to focus on the SELECTand FROMclauses.

PostgreSQL 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 SELECT statement:

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 FROM keyword.

PostgreSQL SELECT examples

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.