This is a guide to PostgreSQL WHERE Clause. This article intends to introduce you to where clause in PostgreSQL. The PostgreSQL WHERE clause is used with SELECT, INSERT, UPDATE, or DELETE statement to filter rows. This SELECT statement would return all firstname and result values where there is a matching record in the student and temp_student tables based on rollno and where the firstname is ‘David’. The above WHERE clause is used to join multiple tables together in a single SELECT statement. SELECT student.firstname, temp_student.result WHERE (lastname = 'David' AND firstname = 'Thomas') OR (rollno >= 103) Example #8 – Combining AND & OR conditions Note that you can use != operator instead of operator. WHERE firstname LIKE 'Ja%' AND lastname 'John' This example finds students whose first name starts with ‘Ja’ and the last name is notJohn.’ Example #7 – Not equal operator () or (! =) In this example, we used the LENGTH () function returns the number of characters of the input string. WHERE firstname LIKE 'J%' AND LENGTH (firstname) BETWEEN 3 AND 5 SELECT firstname, LENGTH (firstname) namelength Note that the BETWEEN operator returns true if a value is in a range of values. The following example finds students whose first names start with the letter J and their lengths are between 3 and 5 by using the BETWEEN operator. The ‘Ja%’ pattern matches any string that starts with ‘Ja’.

The % is called a wildcard that matches any string. The following examples return all students whose names start with the string ‘Ja.’ To find a string that matches a specified pattern, you use the LIKE operator. WHERE firstname IN ('Oliver', 'Jack', 'Harry') For example, the following statement returns students whose ‘firstname’ is ‘Oliver’, ‘Jack’, ‘Harry.’ If you want to match a string with any string in a list, you can use the IN operator. WHERE lastname = 'David' OR firstname = 'Jacob' This example finds the students whose ‘lastname’ is ‘David’ or ‘firstname’ is ‘Jacob’ by using the OR operator. WHERE firstname = 'Thomas' AND lastname = 'David' The following example finds the student whose first name is Thomas and last names are David by using the AND logical operator to combine two Boolean expressions. This means for the attribute of username, the value is jsmith. The name is username and the value is jsmith. This is an example of a name/value pair: 'username': 'jsmith'. A name/value pair is two values enclosed in quotes. If you want to get all students, whose first names are ‘Thomas.’ JSON data is written as name/value pairs. INSERT INTO student (rollno, firstname, lastname, branch, result, joining_date) Let’s practice with some examples of using the WHERE clause with conditions.

Postgresql json query where clause