Skip to main content

Posts

LIKE Operator in PostgreSQL

There are three different approaches(SIMILAR TO Regular Expressions, POSIX Regular Expressions, and LIKE) to pattern matching provided by PostgreSQL but the LIKE operator is the best one, it is easy to use and safe than other approaches. Using wild-cards, the PostgreSQL LIKE operator matches text values against a pattern. If any column text/value or any expression matches to the pattern then the LIKE operator will return the TRUE, if don't then false.
There many scenarios where we have to apply LIKE operator in PostgreSQL, one of them I am explaining here. Suppose we have a customer table and we need to see some customer details but we don't know the customer's exact name but we remember some part of his first name. Here LIKE operator will help you to find the customer. Suppose customer's full name is Dilip Kumar Singh but you have remembered only Dilip, using LIKE operator, you can search this customer and can get his full details. PostgreSQL allows two wild-cards in …
Recent posts

HAVING clause in PostgreSQL

In this article, we will learn about HAVING clause, its proper use on the scenarios based, how to eliminate groups of rows that do not satisfy a specified condition, etc. The HAVING clause used with GROUP BY clause and it behaves like where clause but applies only to groups as a whole. The HAVING clause is applied to the rows in the result set, means first data is fetched from memory to result set then apply HAVING clause on the row of the result set. We can apply a HAVING clause only to columns that also appear in the GROUP BY clause or in an aggregate function.

Syntax: PostgreSQL HAVING clauseSELECT col_1, aggregate_function (col_2) FROM table_name GROUP BY col_1 HAVING condition;

Some scenarios where we need to apply the having clause Suppose, we have a table where we manage the monthly salary data of employees, for practice please execute the following PostgreSQL statement.
CREATE TABLE public."Salary" ( "Id" bigserial NOT NULL , "EmpId" bigint NOT NULL, "Net…

GROUP BY clause in PostgreSQL

With the assistance of some functions, the GROUP BY statement in SQL is used to organize identical data into groups. i.e. if a specific column has the same values in distinct rows, then these rows will be arranged in a group.  Syntax: the syntax of the GROUP BY clause is given below.
SELECT <Col_1>, <Col_2>, <aggregate_function(Col_3)>....<Col_n> FROM <table_name> WHERE <conditions> GROUP BY <Col_1>, <Col_2>....<Col_N>

For each the group you can apply an aggregate function like SUM(), COUNT(), etc. The GROUP BY clause appears after FROM or WHERE clause in a SELECT statement. PostgreSQL GROUP BY clause examplesSuppose we have the following table. You can create an Employee table using the below PostgreSQL statement.
CREATE TABLE public."Employee" (     "Id" bigserial NOT NULL,     "FName" text COLLATE pg_catalog."default",     "LName" text COLLATE pg_catalog."default",     "UserN…

ORDER BY clause in PostgreSQL

ORDER BY clause used to sort the result set return from the SELECT statement in ascending or descending order based on the specified criteria. Without using ORDER BY clause, when you query data from the table, PostgreSQL returns the rows in the order that they were inserted into the table. In many scenarios, we need to show the result set based on the specified column(s) ascending or descending, suppose I have an Employee table and want to see the employee's records based on their FirstName ( ascending or descending), in that type of cases ORDER BY clause help us to achieve the goal.
PostgreSQL - ORDER BY clause syntax:
SELECT Col_1, Col_2, Col_3, ..... FROM table_name ORDER BY Col_1 ASC, Col_2 DESC;

Suppose we have the following table. You can create an Employee table using the below PostgreSQL statement.
CREATE TABLE public."Employee" ( "Id" bigserial NOT NULL, "FName" text COLLATE pg_catalog."default", "LName" text COLLATE pg_catalog."…

UPDATE in PostgreSQL

Using the UPDATE statement, we can change the value of the existing records in the table. Sometimes we need to change the value of the column(s) as per requirements. Suppose we have the "Employee" table and we need to update the salary of a particular employee, it means we will update the salary which already exists in the "Employee" table. But do it carefully because it is very critical to update any records into the table, you must apply where clause in update statement because if you don't use WHERE clause, then it will update all records of your table which may create a big issue for you. So please be careful before running the UPDATE statement. PostgreSQL UPDATE syntax
UPDATE table_name SET col1 = val1, col2 = val2 ,... WHERE condition;
table_name is the name of the table which records you want to update, and it comes after UPDATE keywords.

col1,col1 is the column name of the table which particular value want to change. It comes after the SET keyword, and if you…

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. OperatorDescription =Equal >Greater than <Less than >=Greater than or equal <=Less than or equal <> or !=Not equal ANDLogical operator AND ORLogical 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"…