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 short 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 using the ORDER BY clause.
• Select rows based on various operators such as BETWEEN, IN and LIKE.
• Group rows into groups by using GROUP BY clause
• Apply condition for groups by using HAVING clause.
• Join to another table by using INNER JOIN, LEFT JOIN, RIGHT JOIN clauses.

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

PostgreSQL SELECT statement syntax

Let’s start with a basic form of the SELECT statement to query data from a  table. The following illustrates the syntax of the SELECT statement:

Let’s examine the SELECTstatement in more detail:

• First, you specify a list of columns in the table from which you want to query data in the SELECTclause. You use a comma between each column in case you want to query data from multiple columns. If you want to query data from all column, you can use an asterisk (*) as the shorthand for all columns.
• Second, you indicate the table name after the FROM keyword

Notice that SQL language is case insensitive. It means if you use SELECT or select the effect is the same. By convention, we will use SQL keywords in uppercase to make the code easier to read and stand out clearly.

PostgreSQL SELECT examples

Let’s take a look at several examples of using PostgreSQL SELECT statement to query the data from the customers table in the sample database.

To query data from all rows and all columns from 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 SQL statement. It is only for PostgreSQL to specify the end of an SQL statement.

It is not good practice to use the asterisk (*) in the SELECT statement. Imagine that you have a large table with many columns, the SELECT statement with an asterisk (*) will query all the data from the entire columns, which may not necessary. It makes your database server work harder and increase the traffic between the database server and applications. As the result, it slows down your application.  Therefore, you should specify the column names in the SELECT clause whenever possible to get only necessary data from a table.

Suppose you just need to know first name, last name and email of customers, you can list 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.