WHERE Clause in PostgreSQL



WHERE clause used into PostgreSQL statement to specify/apply any condition while retrieving, updating or deleting data from a table.

Syntax: PostgreSQL statement using WHERE clause



SELECT <Column Name1>,<Column Name2>...
FROM table_name
WHERE <condition>;


The following table illustrates the standard comparison operators.

Operator                  Description
----------------------------------------------------------------------------------
=                             Equal
>                             Greater than
<                             Less than
>=                           Greater than or equal
<=                           Less than or equal
<> or !=                   Not equal
AND                          Logical operator AND
OR                            Logical operator OR

PostgreSQL WHERE clause example


Suppose we have a table "Employee" so first, we create a table using the following query.


CREATE TABLE public."Employee"
(
    "Id" bigserial NOT NULL,
    "FName" text COLLATE pg_catalog."default",
    "LName" text COLLATE pg_catalog."default",
    "UserName" text COLLATE pg_catalog."default",
    "Password" text COLLATE pg_catalog."default",
    "Contact" text COLLATE pg_catalog."default",
    "Salary" numeric NOT NULL DEFAULT 0.0,
    CONSTRAINT "PK_Test" PRIMARY KEY ("Id")
);

  
Insert some records on the above table ("Employee")


INSERT INTO public."Employee"(
  "FName", "LName", "UserName", "Password", "Contact", "Salary")
  VALUES ('Name1','last1', 'name1@gmail.com', 'name@0123', '9088877787', '20000');

INSERT INTO public."Employee"(
  "FName", "LName", "UserName", "Password", "Contact", "Salary")
  VALUES ('Name2','last2', 'name2@gmail.com', 'name@0123', '9288877787', '30000');
              
INSERT INTO public."Employee"(
  "FName", "LName", "UserName", "Password", "Contact", "Salary")
  VALUES ('Name3','last3', 'name3@gmail.com', 'name@0123', '9388877787', '40000');


Illustrates of WHERE clause


1-   Used WHERE clause when selecting a particular record(s) Query: If you want to select the user which UserName and Password are name1@gmail.com and name@0123


SELECT "Id", "FName", "LName", "UserName", "Password", "Contact", "Salary"
  FROM public."Employee"
  WHERE "UserName"='name1@gmail.com' AND "Password"='name@0123'


2-   Used WHERE clause when updating a particular record: Suppose you want to update Salary of the employee Name2 then use the below statement


UPDATE public."Employee"
  SET "Salary"=50000
  WHERE "UserName"='name2@gmail.com' AND "Password"='name@0123'


3-   Used WHERE clause when deleting a particular record(s): Suppose you want to delete 3rd-row record (I am deleting record using Id)


DELETE FROM public."Employee"
 WHERE "Id" = 3;



NUnit : Introduction

There are several unit test tools in the market, but one of them is NUnit, the most popular tool for doing unit testing. So in this tutorial...