Skip to main content

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 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 particular record: Suppose you want to update Salary of 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;



Popular posts from this blog

Add day to ISODate in MongoDB

We can use $add operator to add days in ISODate in mongodb, $add is the Arithmetic Aggregation Operator which adds number and date in mongodb.
Syntax:

{ $add: [ <expression1>, <expression2>, ... ] }

Note:  If one of the argument is date $add operator treats to other arguments as milliseconds to add to the date.
Example: Suppose we have a Test collection as below.

{"Title" : "Add day to ISODate in MongoBD","CreatedDate" : ISODate("2016-07-07T08:00:00.000Z")}

Query to add 2 days in CreatedDate

db.Test.aggregate([      { $project: { Title: 1, AddedDate: { $add: [ "$CreatedDate", 2*24*60*60000 ] } } }    ])

Result:

{ "_id" : ObjectId("579a1567ac1b3f3732483de0"), "Title" : "Add day to ISODate in MongoBD", "AddedDate" : ISODate("2016-07-09T08:00:00.000Z") }

Note: As mentioned in above note we have to convert days in millisecond because $add operator treat to other arg…

Remove special characters from string in SQL server

I faced many times an issue to remove special characters from a string. Suppose you are working on searching concept and you have to remove the special characters from search string due to query performance, there are many solution are available but T-SQL is easily resolved this issue.
Following query may help you to resolve your issue.

DECLARE@strVARCHAR(400) DECLARE@expresVARCHAR(50)='%[~,@,#,$,%,&,*,(,),.,!]%' SET@str='(remove) ~special~ *characters. from string in sql!' WHILEPATINDEX(@expres,@str)> 0 BEGIN SET@str=Replace(REPLACE(@str,SUBSTRING(@str,PATINDEX(@expres,@str), 1 ),''),'-',' ') END SELECT@str