PostgreSQL Select Where Statement

Have a Database Problem? Speak with an Expert for Free
Get Started >>

Introduction

The PostgreSQL SELECT statement is a very useful tool for displaying the records of a specific PostgreSQL table. However, for displaying the value of a specific existing record that matches a certain set of conditions, the WHERE clause can be used in conjunction with the SELECT statement. The WHERE clause is used to filter the data and extract only those records that meet the said specified set of criteria. This is an equally useful function when wanting to limit the number of rows that are returned within a given query. This tutorial will explain how to use the PostgreSQL SELECT WHERE statement to locate only those records that meet a given set of conditions.

Prerequisites

  • PostgreSQL must be properly installed and configured on the local system to execute the examples provided in this tutorial.

  • A basic working knowledge of PostgreSQL is required in order to follow the instruction for using the PostgreSQL SELECT WHERE statement.

PostgreSQL Select Where Syntax

Following is the syntax for the PostgreSQL SELECT statement:

1
SELECT * FROM TABLE_NAME;

Following is the syntax for the PostgreSQL SELECT statement with the WHERE clause:

1
SELECT * FROM TABLE_NAME WHERE condition;

Following is a detailed breakdown of how the above syntax functions:

  • First, the name of the column(s) are listed. Alternatively, an asterisk (*) can be used after the SELECT statement as this is equivalent to selecting all of the columns.

  • Second, specify the name of the table to be displayed after the FROM clause.

  • Third, use the WHERE clause to include a condition, if desired or required.

PostgreSQL Select Where Clause Example

The below example will use the table name “Countries.” Here the SELECT statement clause will be used to display all of the records on the table by executing the following query:

1
SELECT * FROM countries;

The results of the above query should resemble the following:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
 country_id |       country_name       |   continent   | code
------------+--------------------------+---------------+------
         35 | South Africa             | Africa        | AF
         14 | Canada                   | North America | NA
         51 | Cuba                     | North America | NA
         41 | Mexico                   | North America | NA
         45 | United States OF America | North America | NA
         85 | Argentina                | South America | SA
         37 | Venezuela                | South America | SA
         63 | Philippines              | Asia          | AS
         83 | China                    | Asia          | AS
         22 | Russia                   | Asia          | AS
         21 | United Arab Emirates     | Asia          | AS
         62 | Germany                  | Europe        | EU
         73 | Italy                    | Europe        | EU
         28 | United Kingdom           | Europe        | EU

Now add the following condition to the query that will only display the records of the continent “North America:”

1
2
SELECT * FROM countries
WHERE continent = 'North America';

The result of the above query should resemble the following:

1
2
3
4
5
6
 country_id |       country_name       |   continent   | code
------------+--------------------------+---------------+------
         14 | Canada                   | North America | NA
         51 | Cuba                     | North America | NA
         41 | Mexico                   | North America | NA
         45 | United States OF America | North America | NA

Following is another example that will this time display the records of the continent “Europe”, but only where the column “country_id” is greater than 50, as shown in this query:

1
2
3
SELECT * FROM countries
WHERE continent = 'Europe'
AND country_id > '50';

The result of the above query should resemble the following table:

1
2
3
4
 country_id | country_name | continent | code
------------+--------------+-----------+------
         62 | Germany      | Europe    | EU
         73 | Italy        | Europe    | EU

Note that the only countries returned are located in Europe and have an ID greater than 50.

Conclusion

This tutorial explained how to use the PostgreSQL SELECT WHERE statement to locate only those records that meet a specified set of conditions. The tutorial provided the syntax for the PostgreSQL SELECT statement, the syntax for the SELECT statement used with the WHERE clause and provided a breakdown of the two. The article then provided three examples for using the PostgreSQL SELECT WHERE statement to locate only those records that meet a specified set of conditions. Remember that the WHERE clause is only used to include some type of a condition on the search query when required. Also bear in mind that an asterisk can be used after the SELECT statement to select all of the columns as an alternative to listing all of the names of the columns.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.