PostgreSQL Select Where Statement
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