DEV Community: Malik M

Webinar

Malik M — Thu, 14 Sep 2023 15:33:38 +0000

Ever wondered how #PostgreSQL can be combined with graph technologies can shape enterprise data management solutions?
Join the webinar :
Registration link: https://us06web.zoom.us/webinar/register/WN_PohMAFj0RJuJPAw8bTq1MA#/registration

GROUP BY clause in PostgreSQL

Malik M — Thu, 17 Aug 2023 21:09:17 +0000

In this tutorial we will learn about the use of GROUP BY clause.
So, let's get started...
It is used to divide the rows in groups that are return from the SELECT statement. You can then apply aggregate functions such as 'SUM()' or 'COUNT()' to each group to find sum or number of items in the groups.

Syntax

SELECT 
   column_1, 
   column_2,
   ...,
   aggregate_function(column_3)
FROM 
   table_name
GROUP BY 
   column_1,
   column_2,
   ...;

The above statement shows the syntax of the 'GROUP BY' clause.
In this syntax, first select the columns that you want to group and apply the aggregate function such as here we can see I have used 'column_1', 'column_2' in the above syntax.
Second list all the columns in the 'GROUP BY' that you want to group.

PostgreSQL evaluates the 'GROUP BY' after the 'FROM' and 'WHERE' clauses but it is evaluated before 'HAVING', 'SELECT', 'DISTINCT', 'ORDER BY' and 'LIMIT' clauses.
This can be seen through the following image:

Examples
1) GROUP BY without an aggregate function:
We can also use the 'GROUP BY' without using any aggregate function. Let's see an example:

SELECT
   customer_id
FROM
   payment
GROUP BY
   customer_id;

In the above code it gets the data from the 'payment' table and group them by the customer id. Here it acts like distinct and removes all the duplicate results from the set.

Output

2) GROUP BY with SUM() function:

SELECT
    customer_id,
    SUM (amount)
FROM
    payment
GROUP BY
    customer_id;

To calculate and select the total amount each customer has paid, we can use the 'GROUP BY' clause to first divide the rows in groups in the 'payment' table and then for each group sum the total amount.

Conclusion:
In this article we learned about 'GROUP BY' clause.

UPDATE statement in PostgreSQL

Malik M — Thu, 17 Aug 2023 20:28:14 +0000

In this tutorial, We will be exploring how to update the data in an existing table.
Let's get started...

The UPDATE statement is used to modify the data in an existing table.

Syntax

UPDATE table_name
SET column1 = value1,
    column2 = value2,
    ...
WHERE condition;

In the above syntax, we can see that first we have specify the table name with the UPDATE keyword.
Second, we have to specify the columns that we want to update and their updated values after 'SET' keyword. Also note that those columns that do not appear in the 'SET' clause won't be modified.
Third, we have to write the conditions which rows to be updated in the table after the 'WHERE' clause.

If you want to return the row that is/are just modified use the following code in the end:

RETURNING *;

EXAMPLE
Let's look at the example and practice it.
Suppose we have the following table which shows all the courses and description:

1) Update one row from table
The following query will modify one row from the table that has id=2 and sets it published_date to 2020-07-01.

UPDATE courses
SET published_date = '2020-07-01'
WHERE course_id = 2
RETURNING *;

OUTPUT

Conclusion

In this article we learnt about the 'UPDATE' statement and it's example.

PostgreSQL - History and Common Use cases

Malik M — Thu, 17 Aug 2023 19:07:11 +0000

In this article I will tell you about:

What is PostgreSQL?
History of PostgreSQL?
Common use cases of PostgreSQL

So let's get started with first question.

What is PostgreSQL?
PostgreSQL is an advanced open-source relational database system that supports both relational (SQL) and non-relational (JSON) queries. It is one of the highly stable database as it was build more than 20 years ago and it is backed by the open-source community.
It is used as a primary database not just for the web applications but also mobile and analytics applications too.

So this was a little introduction of the PostgreSQL.
Now let's look at the history of PostgreSQL.

History
PostgreSQL originally named as POSTGRES was started in 1986 at Berkeley Computer Science Department, University of California.
The name POSTGRES was in reference to the Ingres that is also a database developed in Berkeley. POSTGRES main goal was to add features needed to support multiple datatypes.
It was renamed to PostgreSQL in 1996 to show it's support for the SQL.
Did you know that it was originally designed to run on UNIX-like programs only. But as the time passed, it was evolved to run on various platforms like macOS, Windows and Solaris.

Now let's dive into some of the use cases of the PostgreSQL also known as Postgres.

Common use cases

Robust database:
It is used as primary back-end database because of it's robustness to power dynamic websites and web applications.
General purpose:
It is used in large number of organizations and startups to support their real life products and applications.
Geospatial database:
It can help in storing, indexing and querying the geographical data with the PostGIS extensions for the geographic information system.

Languages it support
It supports almost all the popular languages such as Python, Java, JavaScript, C/C++, Ruby, Go, Perl and Tcl.

INSERT statement in PostgreSQL

Malik M — Thu, 17 Aug 2023 18:34:45 +0000

In this tutorial, We will be exploring how to add new row in the existing table.
Let's get started...

The INSERT statement is used to insert a new row in the table.

Syntax

INSERT INTO table_name(column1, column2, …)
VALUES (value1, value2, …);

In the above syntax, we can see that first we have specify the table name with the INSERT INTO keywords and the table will have list of comma-separated columns in the brackets.
Second, we have to provide values against each column after VALUES keyword, that must be in the same orders as columns are.
If you want to return the row that is just inserted use the following code in the end:

RETURNING *;

If you want to return just some information, just specify the one or more column names after 'RETURNING' keyword like this:

RETURNING id;

In the above code I am returning ID only.

EXAMPLE
Getting the last insert 'id':
Suppose we want to return the id of the last in the table Named links that has url and the name, 'id' and 'description' as columns.

INSERT INTO links (url, name)
VALUES('http://www.postgresql.org','PostgreSQL') 
RETURNING id;

In the above code we are inserting 'url' and 'name' values and returning the id of the last insert.
OUTPUT

Conclusion

In this article we learnt about the 'INSERT' statement and it's example.

DELETE statement in PostgreSQL

Malik M — Tue, 15 Aug 2023 18:53:30 +0000

In this tutorial we will learn about the use of DELETE statement.
So, let's get started...
Use of DELETE statement
DELETE statement is used to delete one or more rows from a table.
Syntax

DELETE FROM table_name
WHERE condition;

In the above syntax first we need to specify the table from which we want to delete the rows.
Second we have to specify the condition in the WHERE clause to delete the rows.
Note one thing here, 'WHERE' clause is optional, if we omit the 'WHERE' clause, it will delete all the rows in the table.

Example
Let's look at the example and practice it.
Suppose we have the following table which shows all the links to websites:

1) DELETE one row from table
The following query will delete one row from the table.

DELETE FROM links
WHERE id = 8;

The above query deletes the row which has id=8.

2) Delete a row and return the deleted row
In this example we will see how to return a deleted row:

DELETE FROM links
WHERE id = 7
RETURNING *;

In the above syntax row which has id=7 is deleted. The RETURNING statement at the end returns the deleted row.
Output is as:

Conclusion
In this tutorial we explored about the 'DELETE' statement in PostgreSQL.

EXISTS in PostgreSQL

Malik M — Tue, 15 Aug 2023 18:35:18 +0000

In this tutorial, I will be explaining how can we use the EXISTS in a subquery.
Let's get started...
EXISTS is a boolean operator, and it tests for the existence of rows in a subquery.
Syntax
Following is the syntax of using EXISTS:

EXISTS (subquery)

The EXISTS operator accepts a subquery as an argument.
Result of EXISTS is true if the subquery returns at least one row.
Result of EXISTS is false if the subquery returns no row.

Let's see an example:
For example we have following customer and payment tables in the database.

1) Find customers whose at least one payment is greater than 11

SELECT first_name,
       last_name
FROM customer c
WHERE EXISTS
    (SELECT 1
     FROM payment p
     WHERE p.customer_id = c.customer_id
       AND amount > 11 )
ORDER BY first_name,
         last_name;

In this, subquery checks payment table for each customer and find that if there is any customer that has at least one payment that has amount greater than 11.
The above query returns the following output:

2) NOT EXISTS
The NOT EXIST is opposite to EXISTS operator, that means the result of NOT EXISTS is true if the subquery returns no row and vice versa.

SELECT first_name,
       last_name
FROM customer c
WHERE NOT EXISTS
    (SELECT 1
     FROM payment p
     WHERE p.customer_id = c.customer_id
       AND amount > 11 )
ORDER BY first_name,
         last_name;

The above subquery checks payment table for each customer and find that if there is any customer that has not made any payment amount greater than 11.

The output of the query is as:

Conclusion
In this tutorial we explored the basic level use of EXISTS operator.

Finding COUNT, SUM, and AVG in PostgreSQL

Malik M — Sun, 30 Jul 2023 20:43:44 +0000

In this tutorial, I will be explaining how can we find the count, sum and average in PostgreSQL query.
Let's get started...

Finding Count of rows
In this section we will explore how can we find the count of rows.
The COUNT() function returns the number of rows that matches a specified criteria.

Syntax

SELECT COUNT(column_name)
FROM table_name;

Let's understand by an example:
Suppose we want to find the number of customers from the London in our Customer table.

SELECT COUNT(customer_id)
FROM customers
WHERE city = 'London';

The above query first find the rows which satisfies the WHERE clause condition. Then counts the total number of rows and returns a number.

OUTPUT

Finding SUM
In this section we will explore how can we use SUM() function.
The SUM() function returns the total sum of a numeric column.

Syntax

SELECT SUM(column_name)
FROM table_name;

Let's understand by an example:
Suppose we want to find the the sum of the quantity fields in the order_details table:

SELECT SUM(quantity)
FROM order_details;

The above query returns the total number of items which are in ordered table.

OUTPUT

Finding AVG
In this section we will explore how can we use AVG() function.
The AVG() function returns the average value of a numeric column.

Syntax

SELECT AVG(column_name)
FROM table_name;

Let's understand by an example:
Suppose we want to find the the average price of all the products in the product table:

SELECT AVG(price)
FROM products;

The above query returns the average price of all the products

OUTPUT

Conclusion
In this table we learnt about how we can use Count, SUM and AVG in a query.

Column Alias in PostgreSQL

Malik M — Sun, 30 Jul 2023 19:55:07 +0000

In this article I will share how we can assign a column alias and it's in the PostgreSQL.
Let's get started...
Column alias is used to assign a temporary name to a column or an expression in the PostgreSQL. It exists only temporarily during the execution of query.

Syntax of using a Column Alias

SELECT column_name AS alias_name
FROM table_name;

As from the syntax, it can be seen that column_name is assigned an alias alias_name. Here AS key is used to assign the alias name. AS key is optional to use. We can omit it as well. The syntax will be then as:

SELECT column_name alias_name
FROM table_name;

Example
Suppose we have the customer table with following attributes in the database:

1. Assigning a column alias to a column:

SELECT 
   first_name, 
   last_name AS surname
FROM customer;

The above query assigned the surname as column alias to the last_name. If we have not used column alias then the name of the column would have appeared as "last_name".

OUTPUT:

2. Assigning a column alias to an expression:

SELECT 
   first_name || ' ' || last_name AS full_name 
FROM 
   customer;

The above query returns full names of all the customers. The expression first_name || ' ' || last_name AS full_name constructs the full name by concatenating the first name, space and the last name.

OUTPUT:

Conclusion
In this tutorial we learnt about how we can assign temporary names to the queries using AS in our query statement.

USE of ORDER BY in PostgreSQL

Malik M — Sun, 30 Jul 2023 19:28:49 +0000

In this article I will share how Order By clause can be used in the PostgreSQL.
Let's get started...
When a query is run to return the rows as output in a SELECT statement, it returns data in unspecified order. So ORDER BY clause is used to sort the data of the output.

Syntax of ORDER BY clause

SELECT
    select_list
FROM
    table_name
ORDER BY
    sort_expression1 [ASC | DESC];

As from the syntax, it can be seen that first step is to specify a sort expression that can be a column or expression which you want to sort just after the ORDER BY clause.
After specifying the sort expression, ASC or DESC is used to sort the rows of output in ascending or descending order based on the desired output you want.
It is noted that if you miss writing the ASC or DESC option then by default ASC is used.

Example
Suppose we have the customer table with following attributes in the database:

1. Using ORDER BY to sort rows by column:

SELECT
       first_name,
       last_name
FROM
       customer
ORDER BY
       last_name DESC;

The above query returns the rows after those rows by values in the last name column in descending order.

OUTPUT:

2. Using ORDER BY to sort rows by expressions:

SELECT 
    first_name,
    LENGTH(first_name) len
FROM
    customer
ORDER BY 
    len DESC;

The above query returns first select the first names and their length. And then It sorts the rows by the lengths of the first names in descending order. The name with highest length will be on the top and so on.

OUTPUT:

Conclusion
In this tutorial we learnt about the use of ORDER BY clause in PostreSQL by column as well as by expressions.

Advantages of PostgreSQL

Malik M — Sun, 30 Jul 2023 19:04:00 +0000

In this article I will be sharing the benefits of using PostgreSQL as database. So let's just get started...
Introduction
PostrgreSQL is a free and open-source advanced relational database system. It supports relational as well as non-relational (JSON) queries.
Let's look at some of the advantages of PostrgreSQL which makes it standout from other databases.
Advantages

PostrgreSQL is flexible solution with extension which can cover every potential use case. You can even write your own extension if you have a very particular requirement such as support for a data type or some special logging functionality.
PostrgreSQL can be used as both relational database management system (SQL) or as a NOSQL database solution such as storing the JSON documents. This flexibility of PostrgreSQL can reduce the cost. Using single database management system means that it cut the cost of managing, setting up and updating multiple database solutions. All can be done from a single place.
For critical productions such as hospitals, government agencies and financial firms, the privately supported solutions of PostrgreSQL can be used. These versions of PostrgreSQL provides additional security features, high-availability and resiliency.

So above mentioned are some of the benefits of using PostgreSQL.

PostgreSQL vs MySQL

Malik M — Fri, 28 Jul 2023 14:40:02 +0000

In this article I will be explaining the difference between PostgreSQL and MySQL based on their architecture and performance.
Let's get started...

Introduction:
PostgreSQL and MySQL are two popular choices whenever we want to choose the Database Management System for the application.
Both of these databases has their own strengths to cater the different use cases.
Both are relational database management systems (RDBMS), but PostgreSQL stands out as an object-relational DBMS (ORDBMS) with advanced capabilities.

1. Architecture and Performance:

PostgreSQL:
As PostgreSQL has ORDBMS architecture it provides advantage over MySQL. PostgreSQL does not requires read or write locks, which allows multiple users to access and modify data simultaneously without any inconvenience. These features of the PostgreSQL make it ideal choice for the environment where there are multiple users working simultaneously on it.

MySQL:
MySQL relies on write locks to achieve concurrency. It can lead to a reduction in the number of concurrent operations per process, which reduces its performance in write-heavy scenarios. To make it well suited for write heavy scenarios, considerable resources are needed to be added for that. And MySQL excels in read-heavy operations which makes it suitable solution for applications which are simple and relies only on data retrieval operations for the most of the time.

2. Speed vs. Data Integrity:

MySQL:
MySQL's design prioritizes speed over data integrity and standards compliance. In simple implementations, MySQL can outperform PostgreSQL due to its lightweight approach and lower memory usage. For applications that primarily read and display information from the database, MySQL may offer superior performance.

PostgreSQL:
PostgreSQL, on the other hand, sacrifices some speed for the sake of data integrity and compliance with industry standards. when creating a new process it allocates a large amount of memory (almost 10MB) for each connection, which can lead to high memory usage. Well, this trade-off ensures that data is handled with strict adherence to consistency and reliability, making PostgreSQL a preferable choice for applications where data accuracy and reliability are critical.

3. Write-Heavy Operations and Data Warehousing:

PostgreSQL:
PostgreSQL excels in write-heavy operations, which makes it well suited for applications which requires large number of write transactions. It also supports concurrent writes and robust transaction management. This makes it ideal for systems where real-time data updates are required.

MySQL:
As discussed above MySQL performs well in only read-heavy scenarios. Whereas PostgreSQL performs well in the write heavy scenario. As PostgreSQL is an ORDBMS, this makes it a favorable choice for data warehousing solutions.

Conclusion:
In short, PostgreSQL and MySQL, both are powerful database management systems, but their strengths lie in different areas. PostgreSQL is an excellent choice for the reliable, and database which is feature-rich to handle large-scale and complex applications. Whereas, MySQL is best choice for the applications which are simpler and read heavy operations are the critical part of it.