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
- 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
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
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
Notice that SQL language is case insensitive. It means if you use
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:
SELECT * FROM customer;
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.