YogeshChauhan.com
WHERE Clause in Postgres
May 1, 2020

WHERE condition is any expression that evaluates to a result of type boolean. Any row that does not satisfy this condition will be eliminated from the output.

General form:


WHERE condition

Syntax:


SELECT column1, column2, ...
FROM table
WHERE condition;

Just like SQL, WHERE clause comes right after the FROM clause of the SELECT statement.

We can use comparison or logical operators. like >, <, =, LIKE, NOT, etc in the WHERE condition. The condition must evaluate to true, false, or unknown. It can be a Boolean expression or a combination of Boolean expressions using AND and OR operators.

List of Comparison Operators
OperatorName
=Equal
>Greater than
<Less than
>=Greater than or equal
<=Less than or equal
<> or !=Not equal
ANDLogical operator AND
ORLogical operator OR

I am using the database available on my public Github repo

Simple WHERE example:


SELECT customer_id, contact_name FROM customers WHERE country= 'USA';

//Output

customer_id.    contact_name
"GREAL"	"Howard Snyder"
"HUNGC"	"Yoshi Latimer"
"LAZYK"	"John Steel"
"LETSS"	"Jaime Yorres"
.....
.....

Example with OR operator:


SELECT customer_id, contact_name FROM customers WHERE country= 'USA' OR country='France';

//Output

customer_id.   contact_name
"BLONP"	"Frédérique Citeaux"
"BONAP"	"Laurence Lebihan"
"DUMON"	"Janine Labrune"
"FOLIG"	"Martine Rancé"
...
...

Above Example with IN operator, same output:


SELECT customer_id, contact_name FROM customers WHERE country IN('USA', 'France');

//Output

customer_id.   contact_name
"BLONP"	"Frédérique Citeaux"
"BONAP"	"Laurence Lebihan"
"DUMON"	"Janine Labrune"
"FOLIG"	"Martine Rancé"
...
...

Example with LIKE operator:


SELECT customer_id, contact_name FROM customers WHERE country LIKE 'US%';

//Output

customer_id.   contact_name
"GREAL"	"Howard Snyder"
"HUNGC"	"Yoshi Latimer"
"LAZYK"	"John Steel"
"LETSS"	"Jaime Yorres"
...
...

Example with BETWEEN operator:


SELECT customer_id, contact_name FROM customers WHERE LENGTH(country) BETWEEN 2 AND 5;

//Output

customer_id.  contact_name

"AROUT"	"Thomas Hardy"
"BOLID"	"Martín Sommer"
"BSBEV"	"Victoria Ashworth"
"CONSH"	"Elizabeth Brown"
...
...

In the above example, I am just asking Postgres for customer_id and customer_name from the countries that has country_name length BETWEEN 2 and 5.

Example with not equal operator (<>):


SELECT customer_id, contact_name FROM customers WHERE country = 'USA' AND region <> 'SP';

//Output

customer_id.  contact_name.  region

"GREAL"	"Howard Snyder"	"OR"
"HUNGC"	"Yoshi Latimer"	"OR"
"LAZYK"	"John Steel"	"WA"
"LETSS"	"Jaime Yorres"	"CA"
...
...
dreamhost

Leave a Reply

Most Read

#1 How to check if radio button is checked or not using JavaScript? #2 How to set opacity or transparency using CSS? #3 Pagination in CSS with multiple examples #4 How to make HTML form interactive and using CSS? #5 Solution to “TypeError: ‘x’ is not iterable” in Angular 9 #6 How to uninstall Cocoapods from the Mac OS?

Recently Posted

Mar 3 Window setInterval() Method in JavaScript Mar 2 How to zoom an element on hover using CSS? Mar 2 the box-sizing property in CSS Mar 2 WordPress: How to access first and random row values from a repeater field in ACF? Mar 1 The basics of CSS Box model Feb 27 WordPress: How to display slider repeater fields in ACF?

You might also like these

What Is a Graph Database?MiscHow to set default timezone using PHP?PHPtransform-origin Property in CSSCSSWhat is the best way to add JavaScript Code into HTML?HTMLWordPress: How to access first and random row values from a repeater field in ACF?WordPressHow to Validate User Name, Email Address and URL in PHP?PHP