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;
|
