Skip to main content

Posts

ROLLUP sub-clause in PostgreSQL

PostgreSQL ROLLUP is a sub-clause of the GROUP BY clause that generates a result set which shows the aggregate for the hierarchy of values in the selected columns. It allows multiple groupings set the same as CUBE sub-clause, but there is only one difference between CUBE and ROLLUP. CUBE sub-clause generates a result set that shows aggregate for all combinations of values in the selected columns ROLLUP is a sub-clause of the GROUP BY clause that generates a result set which shows the aggregate for the hierarchy of values in the selected columns.
CUBE(Col_1, Col_2, Col_3) --------------------------------- Col_1, Col_2, Col_3 Col_1, Col_2 Col_1, Col_3 Col_1 Col_2,Col_3 Col_2 Col_3 ()


ROLLUP(Col_1, Col_2, Col_3) --------------------------------- Col_1, Col_2, Col_3 Col_1, Col_2 Col_1 ()

Syntax of the ROLLUP sub-clause:
SELECT c1, c2, c3, aggregate (c4) FROM table_name GROUP BY ROLLUP (c1, c2, c3);

Query is same as GROUP BY except added a new ROLLUP sub-clause

CUBE sub-clause in PostgreSQL

PostgreSQL CUBE is a sub-clause of the GROUP BY clause which allows us to generate multiple grouping sets. As we know grouping set is a set of columns to which you want to group, but we can not create multiple grouping set in a single statement (multiple statements may be the cause of slow query performance), CUBE sub-clause resolve these issues. Syntax of the CUBE sub-clause:

SELECT c1, c2, c3, aggregate (c4) FROM table_name GROUP BY CUBE (c1, c2, c3);

The query is the same as GROUP BY except we added a new CUBE sub-clause after GROUP BY clause. The query statement generates all possible grouping sets based on the dimension columns specified in CUBE. If the number of columns specified in the CUBE is n, then you will get the result in 2n combinations. Note: PostgreSQL allows you to perform a partial cube to reduce the number of aggregates calculated. We can understand the use of the CUBE sub-clause by example. Suppose we have a PRODUCTS table with columns like product name, category, quantity,…

UNION and UNION ALL in PostgreSQL

UNION Operator in Postgres The UNION operator is responsible to combine result sets of two or more than two SELECT statements into a single result set. It also removes all the duplicate row. Syntax of UNION Operator
SELECT Col_1, Col_2 FROM Tbl_1 UNION SLECT Col_1, Col_2 FROM Tbl_2 UNION SELECT Col_1, Col_2 FROM Tbl_3

When using the UNION operator in PostgreSQL we have to follow some rules. 1- All Queries (Which SELECT statements result set wan to combine) should return same number of columns 2- The corresponding columns in the queries must have compatible data types (For example Col_1 of Tbl_1 and Col_1 of Tbl_2 should be compatible). UNION Operator in Postgres The UNION ALL operator is also responsible to combine the result sets of two or more than two SELECT statements into a single result set but it does not remove the duplicate row like UNION operator. Syntax of UNION ALL operator
SELECT Col_1, Col_2 FROM Tbl_1 UNION ALL SLECT Col_1, Col_2 FROM Tbl_2 UNION ALL SELECT Col_1, Col_2 FROM Tbl…

JOINS in PostgreSQL

As per the requirement of the project data, normalization, and table relationships are two factors that come to our mind when creating the database schema, it means we minimize the redundancy from a relation or set of relations, and for the referential integrity between them, we draw the relationship. For the user-friendly application's interface when we obtain the records from the database sometimes we need to combine the column from one (self-join) or more tables based on the values of the common columns between the tables. Using PostgreSQL JOIN we combine the column from two or more tables, based on a related column between them. JOINs available in PostgreSQL are:- 1- INNER JOIN                   2- LEFT JOIN 3- RIGHT JOIN 4- FULL OUTER JOIN 5- CROSS JOIN 6- NATURAL JOIN 7- SELF-JOIN To understand the concept of the JOIN I am creating the following table and make a relationship between them.
-- Employee table CREATE TABLE public.employee (     empid serial NOT NULL,-- primary …