“LIKE” vs “ILIKE” operator in PostgreSQL

In this article, we will walk through the “LIKE” and “ILIKE” operators used in PostgreSQL.

PostgreSQL is a case-sensitive language in order to make it a case-insensitive language during pattern matching we make use of the ILIKE operator.

ILIKE operator works the same way as a LIKE operator but makes the language case-insensitive. Both the operators are used for pattern matching in PostgreSQL.

Pattern matching operators

1) LIKE (~~)

Syntax: WHERE value LIKE ‘xyz%’

2) NOT LIKE (!~~)

Syntax: WHERE value NOT LIKE ‘xyz%’

3) ILIKE (~~*)

Syntax: WHERE value ILIKE ‘xyz%’

4) NOT ILIKE (!~~*)

Syntax: WHERE value NOT…

“WHERE” vs “HAVING” clause in PostgreSQL

In this article, we will walk through the syntax, usage of the “HAVING” and “WHERE” clauses, differences between the two, and order of execution.

“HAVING” clause: It is used to filter records from the groups based on the specified condition.

“WHERE” clause: It is used to filter records from the table based on the specified condition.

Syntax of “WHERE” and “HAVING” clause

“HAVING” Syntax:

SELECT column1, column2, aggregate_function(expr) FROM
table GROUP BY column1,column2 HAVING condition;

“WHERE” Syntax:(with GROUP BY)

SELECT column1, column2, aggregate_function(expr) FROM
table WHERE condition GROUP BY column1, column2;

“WHERE” Syntax:(without GROUP BY)

SELECT column1, column2, aggregate_function(expr) FROM…

In this article, we will see how to set up PostgreSQL version-13 on your windows machine and what are the new features added in this version.

How to install and setup PostgreSQL 13 on a Windows machine?

In this article, we will walk through how to perform web scraping using Python from scratch.

To understand the working of choose() function and case statements in SQL Server.

Let’s first understand the syntax (how we write choose() function and case statements in SQL Server) then we will check the performance between the two with bulk data.

Consider a table called Emp_Details having columns Id, Name, and DOB(date of birth).

CREATE TABLE Emp_Details

(

Id int,

Name varchar(20),

DOB date

);

Let’s add some data to Emp_Details

INSERT INTO Emp_Details VALUES

(1,’Mark’,’1980–01–11'),(2,’John’,’1981–12–12'),(3,’Amy’,’1979–11–21'),

(4,’Ben’,’1978–05–14'),(5,’Sara’,’1970–03–17'),(6,’David’,’1978–04–05');

Problem Statement:- Write an SQL query to fetch Name, DOB, and MonthName from DOB.

Solution:-

1. By using CHOOSE() function

SELECT Name, DOB,

CHOOSE(DATEPART(MONTH,DOB),’Jan’,’Feb’,’March’,’April’,’May’,’April’

…

A CSV (Comma Separated Values) file uses commas to separate different values within the file.

The CSV file is a standard format when importing data to a table or exporting data to our system.

In order to add bulk entries from CSV files to databases like Mysql, Postgresql, SQL Server, Oracle, etc… This involves a two-step process:

Step:1 Reading CSV file from the system.

Step:2 Loading CSV data to the database tables.

In this article, we will see bulk data insertion in Mysql, Postgresql, Cassandra, and SQL Server.

1. SQL Server Database

Let’s consider a table called contacts and having following…

PDF and Word documents are binary files, which makes them much more complex than plaintext files. There are Python modules that make it easy for us to interact with PDFs and Word documents.

First Python module: PyPDF2

Second Python module: Python-Docx

To merge multiple pdf files into one pdf file python makes use of a module called PyPDF2 or Python-Docx.

In this article, we will use module PyPDF2

Step:-1 Create a folder named PyPDF, add a file app.py, open the terminal and execute the following command

# Install PyPDF2 module

pipenv install PyPDF2

Step:-2 Open app.py …

This article is a complete guide for new beginners to start with the PostgreSQL database.

Let’s start with a brief introduction about the database PostgreSQL is an open-source object-relational database management system. Object relation because it functions with the object as a relational component in the database. It uses SQL(Structured query language)for accessing data from the database.

Advantages and Disadvantages

Following are the advantages and disadvantages of PostgreSQL

Advantages:-

1. Open-source and easy to use
2. Supports ACID property
3. Has user-defined data types
4. Has extension for time-series database(TimescaleDB)
5. Large support for JSON values
6. Has extension for spatial database(PostGIS)
7. Supports multi-version concurrency control(MVCC)model

…