Top Postgres Interview Questions (2024)

1. What is Postgres or PostgreSQL?

2. What is PostgreSQL vs SQL?

3. How to remove duplicate rows in the result set in Postgres?

4. How to get the subset of rows generated by a query in Postgres?

5. How many joins are there in Postgres to join multiple tables?

6. What is CUBE in Postgres?

7. What is ROLLUP in PostgreSQL?

8. How is CUBE different from ROLLUP in PostgreSQL?

9. What is PostgreSQL CTE?

10. What is PostgreSQL recursive query?

11. What is a database transaction?

12. How to handle transactions in PostgreSQL?

Q: What is Postgres or PostgreSQL?
Ans:

PostgreSQL, sometimes known as Postgres, is a relational database management system that focuses on scalability and SQL compliance. Several web, mobile, geospatial, and analytics applications use PostgreSQL as their primary data storage or data warehouse.

Q: What is PostgreSQL vs SQL?
Ans:

PostgreSQL is a powerful object-relational database management system which use both SQL and its own procedural language, PL/pgSQL. Microsoft SQL Server is a Relational Database Management System (RDBM) that was developed and is maintained by Microsoft.

Q: How to remove duplicate rows in the result set in Postgres?
Ans:

In PostgreSQL, will use the SELECT DISTINCT clause to remove duplicate rows from a result set returned by a query.

SELECT
   DISTINCT column1 -- column1 column are used to evaluate the duplicate.
FROM
   table_name;

SELECT
--column1 and column2 columns will be used for evaluating the duplicate.
   DISTINCT column1, column2

FROM
   table_name;

SELECT
-- DISTINCT ON (expression) to keep the "first" row of each group of duplicates
   DISTINCT ON (column1) column_alias,
   column2
FROM
   table_name
ORDER BY
   column1,
   column2;

Q: How to get the subset of rows generated by a query in Postgres?
Ans:

We can use the PostgreSQL LIMIT clause to get a subset of rows generated by a query.

SELECT select_list
FROM table_name
ORDER BY sort_expression
LIMIT row_count

We can skip a number of rows before returning the row_count rows using OFFSET clause placed after the LIMIT clause:

SELECT select_list
FROM table_name
LIMIT row_count OFFSET row_to_skip;

Q: How many joins are there in Postgres to join multiple tables?
Ans:

PostgreSQL provides support for inner join, left join, right join, full outer join, cross join, natural join, and a new type of join called self-join.

1. Inner Join

   Inner joins combine records from two tables for those records that are matching in both tables (common records).

2. Left Join

   All the rows of the table on the left side of the join are returned, as well as matching rows for the table on the right side of the join. The result-set will include null for the rows for which there is no matching row on the right side.

3. Right Join

   Right joins are identical to left joins except it return all rows from the table in the RIGHT JOIN clause.

4. Full Outer Join

   FULL OUTER JOIN combines the results of both left and right outer joins and returns all (matched or unmatched) rows from both sides of the join clause.

5. Cross Join

   A cross join returns the Cartesian product of rows from the tables included in the join. To put it another way, each row from the first table is combined with each row from the second table.

6. Natural Join

   A NATURAL JOIN is a JOIN action that automatically generates an implicit join clause based on the common fields in the two tables having joined. Columns with the same name in both tables are known as common columns. A NATURAL JOIN can be an INNER join, a LEFT OUTER join, or a RIGHT OUTER join. INNER join is the default.

7. Self Join

   A self-join is a type of join that can be used to join two tables together itself. Each row of the table is joined to itself and all other rows of the same table. As a result, a self-join is mostly used to combine and compare rows from the same database table.

Q: What is CUBE in Postgres?
Ans:

The GROUP BY clause in PostgreSQL has a subclause called CUBE. You can create multiple grouping sets with the CUBE.

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

Q: What is ROLLUP in PostgreSQL?
Ans:

The PostgreSQL ROLLUP clause is a subclause of the GROUP BY clause that allows you to define multiple grouping sets in a single statement. A grouping set is a collection of columns that you can use to group your data.

ROLLUP is often used to calculate data aggregations by year, month, and date, using the hierarchy year > month > date.

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

Q: How is CUBE different from ROLLUP in PostgreSQL?
Ans:

Different from the CUBE subclause, ROLLUP does not generate all possible grouping sets based on the specified columns. It simply makes a subset of those.

The CUBE (c1,c2,c3), for example, creates all eight possible grouping sets:

(c1, c2, c3)
(c1, c2)
(c2, c3)
(c1,c3)
(c1)
(c2)
(c3)
()

However, considering the hierarchy c1 > c2 > c3, the ROLLUP(c1,c2,c3) generates just four grouping sets:

(c1, c2, c3)
(c1, c2)
(c1)
()

Q: What is PostgreSQL CTE?
Ans:

We use the PostgreSQL CTE (common table expressions) to simplify complex queries.

Common table expression is a temporary result set that you may reference within another SQL query like SELECT, INSERT, UPDATE, or DELETE.

Common Table Expressions are temporary in the sense that they exist only while the query is being executed.

WITH cte_name (column_list) AS (
    CTE_query_definition
)
statement;

1. Provide the CTE's name, followed by a column list (optional).
2. Specify a query that produces a result set in the WITH clause's body. If you don't mention the column list after the CTE name, the CTE_query_definition will be used as the CTE's column list.
3. We can use the CTE as a table or view in a statement that could be a SELECT, INSERT, UPDATE, or DELETE.

Example of CTE:

WITH cte_project AS (
    SELECT employee_id,
        COUNT(project_id) project_count
    FROM   project
    GROUP  BY employee_id
)

SELECT s.employee_id,
    first_name,
    last_name,
    project_count
FROM employee s
-- here we used cte
    INNER JOIN cte_project USING (employee_id);

Q: What is PostgreSQL recursive query?
Ans:

A query that refers to a recursive CTE is known as a recursive query. Recursive queries come very handy in various situations, including querying hierarchical data such as organisational structure, bill of materials, and so on.

WITH RECURSIVE cte_name AS(
    CTE_query_definition -- non-recursive term
    UNION [ALL]
    CTE_query definion  -- recursive term
) SELECT * FROM cte_name;

Q: What is a database transaction?
Ans:

A database transaction is a single unit of work that will have one or more tasks or functions to perform.

A transaction in PostgreSQL is atomic, consistent, isolated, and durable. These characteristics are referred to as ACID:

1. Atomicity ensures that the transaction is completed in an all-or-nothing manner.
2. Changes to data written to the database must be valid and adhere to predefined rules in order to remain consistent.
3. The degree to which transaction integrity is visible to other transactions is determined by isolation.
4. Durability ensures that transactions are permanently saved in the database after they have been committed.

Q: How to handle transactions in PostgreSQL?
Ans:

We can handle PostgreSQL transactions using the BEGIN, COMMIT, and ROLLBACK statements.

-- start a transaction
-- we can use BEGIN TRANSACTION or BEGIN WORK or BEGIN
BEGIN;

-- deduct 2500 from account 1
UPDATE accounts
SET balance = balance - 2500
WHERE id = 1;

-- add 2500 to account 2
UPDATE accounts
SET balance = balance + 2500
WHERE id = 2;

-- select the data from accounts
SELECT id, name, balance
FROM accounts;

-- commit the transaction
-- we can use COMMIT WORK or COMMIT TRANSACTION or COMMIT
COMMIT;

-- roll back the transaction
-- we can use ROLLBACK WORK or ROLLBACK TRANSACTION or ROLLBACK
-- ROLLBACK;