In this article, I will take you through 50+ PostgreSQL Interview Questions and Answers for Beginners. When it comes to database interview, PostgreSQL is one of the hot topic from where questions can be asked very frequently due to its large scale use in almost all the major Organizations. Hence it is important to prepare fully before attending any interviews. CyberITHub brings you the latest most frequently asked 50+ PostgreSQL Interview Questions and Answers. More on PostgreSQL docs.

50+ PostgreSQL Interview Questions and Answers for Beginners

Also Read: Top 51 Blockchain Interview Questions and Answers in 2022

1. What is PostgreSQL ?

Ans. PostgreSQL is free and very powerful object-relational database system currently in use with a wide ranges of features available that suits the requirement of modern complex architecture.

2. What are the different user categories in PostgreSQL ?

Ans. PostgreSQL splits user into two categories:-

• Normal Users: These users normally performed action on database objects based on the access privileges given to them.
• Superusers: These users can perform any action on any of the database object.

3. Where does PostgreSQL stores all of its contents ?

Ans. PostgreSQL stores all of its contents on a single filesystem directory known as PGDATA.

4. What is Postmaster ?

Ans. Postmaster is the first process the Cluster executes which in turn is responsible for keeping track of the activities of the whole cluster.

5. What is WALs ?

Ans. WALs or Write-ahead logs is a technology that many databases uses to recover data from a crash. It contains the intent log of database changes.

6. What is pgenv ?

Ans. pgenv is a free and open source tool that allows us to download and manage several instances of different versions of PostgreSQL on the same machine.

7. What is pgbench ?

Ans. pgbench is one of the most popular tool to benchmark a postgresql database.

8. Can a database be dropped if a user is already connected to it ?

Ans. No, a database can only dropped if no users connected to it.

9. Does PostgreSQL 12 has incremental sorting feature ?

Ans. No, it is only included from PostgreSQL 13.

10. Is PostgreSQL ACID compliant ?

Ans. Yes, PostgreSQL is fully ACID compliant database server.

11. What is LibPQ ?

Ans. LibPQ is an underlying library using which every application connects to PostgreSQL Cluster.

12. Which is the main configuration file for PostgreSQL ?

Ans. postgresql.conf

13. Which default user account gets created during the installation of PostgreSQL ?

Ans. postgres

14. How to check current running role in PostgreSQL ?

Ans. The current running role can be checked by using "select current_role" query.

15. Which configuration file contains the firewall for incoming connections ?

Ans. pg_hba.conf

16. Which command can be used to reindex an entire database in PostgreSQL ?

Ans. reindexdb

17. How many savepoints can be used inside a transaction in PostgreSQL ?

Ans. Practically there are no limits. It could be unlimited.

18. How to list all the tables present in the database ?

Ans. Using \d meta-command.

19. How to drop a table in PostgreSQL ?

Ans. drop table <table_name>

20. What are different types of locks that can be used to lock a table in PostgreSQL ?

Ans. There are eight types of locks in PostgreSQL :-

• ACCESS SHARE
• ROW SHARE
• ROW EXCLUSIVE
• SHARE UPDATE EXCLUSIVE
• SHARE
• SHARE ROW EXCLUSIVE
• EXCLUSIVE
• ACCESS EXCLUSIVE

21. What is the default transaction mode in which PostgreSQL runs ?

Ans. READ COMMITTED transaction isolation mode

22. What is the command to drop a database in PostgreSQL ?

Ans. drop database <db_name>

23. Which function can be used to check the size of database ?

Ans. pg_database_size()

24. How many types of tables are there in PostgreSQL ?

Ans. There are three types of tables in PostgreSQL :-

• Temporary Tables - These are very fast tables visible only to the user who created them.
• Unlogged Tables -  These are very fast tables to be used as support tables common to all users.
• Logged Tables -  These are the regular tables.

25. What is NULL in SQL Language ?

Ans. NULL is a special marker used in Structured Query Language to indicate that a data value does not exist in the database.

26. When should we use TRUNCATE command to delete all the table data ?

Ans. When we want to delete all the data from a table without providing a where condition then we can use TRUNCATE command.

27. What is the use of VACUUM operation in PostgreSQL ?

Ans. In a normal operation, tuples that are deleted or obsoleted by an update are not physically removed from the table, they remain present until VACUUM operation is done.

28. Which command will tell psql to show the runtime of a query ?

Ans. timing command

29. Which function can transforms any NULL value into the string ?

Ans. coalesce()

30. What are the different stages in which a SQL statement executes ?

Ans. A SQL statement executes in below four stages:-

• Parser stage - This stage will check for syntax errors and other normal operations.
• Rewrite stage - This stage will take care of system rules
• Optimizer stage - This stage will figure out how to execute a query in the most efficient way
• Plan stage - In this stage, plan provided by the optimizer will be used by the executor to finally create the result.

31. How to limit the number of rows returned by a query ?

Ans. Using limit clause

32. Which System view contains all the statistics about the content of the columns ?

Ans. pg_stats

33. Which command can be used to rewrite a table according to an index ?

Ans. cluster command

34. How to skip a specific number of rows returned by the query ?

Ans. Using offset clause

35. How does a full join is different from a cross join in PostgreSQL ?

Ans. Full Outer Join gives unique result set of left outer join and right outer join of two tables after applying the filter criteria or conditions whereas cross join is simply a cartesian product of two tables irrespective of any filter criteria or conditions.

36. What are different SQL aggregate functions in PostgreSQL ?

Ans. PostgreSQL provides all the below mentioned standard SQL aggregate functions:-

• AVG(): This function returns the average value.
• COUNT(): This function returns the number of values.
• MAX(): This function returns the maximum value.
• MIN(): This function returns the minimum value.
• SUM(): This function returns the sum of values.

37. Which indexes are mostly recommended to index text ?

Ans. Generalized Inverted(GIN) Indexes

38. Which function assigns a progressive number for each row within the partition ?

Ans. ROW_NUMBER() function

39. Which function returns the first value within the partition ?

Ans. FIRST_VALUE() function

40. What is Partitioning in PostgreSQL ?

Ans. Partitioning is a concept in which large tables splits into multiple pieces called partitions. The table that is divided is referred to as a partitioned table.

41. What is Index ?

Ans. An index is an object or structured created from one or multiple columns of different tables to retrieve data faster.

42. Which function ranks the current row within its partition with gaps ?

Ans. RANK() function

43. Which was the first NOSQL data type implemented in PostgreSQL ?

Ans. hstore

44. Which are different types of NOSQL data type handled by PostgreSQL ?

Ans. PostgreSQL handles the following NoSQL data types:

• hstore
• xml
• json

45. What are the main built-in languages supported by PostgreSQL ?

Ans. The main languages ​​supported by PostgreSQL are as follows:

• SQL
• PL/pgSQL
• C

46. What are different ways to handle events in PostgreSQL ?

Ans. There are two ways to handle events in PostgreSQL :-

• By using Rules, or
• By using Triggers

47. Which special variable tells us from which event the trigger is fired ?

Ans. TG_OP variable

48. What are different types of table partitioning currently managed by PostgreSQL ?

Ans. PostgreSQL 12 manages the following types of table partitioning:

• Range partitioning
• List partitioning
• Hash partitioning

49. Which option allows a role to access all the data within the cluster without any particular restriction ?

Ans. REPLICATION property

50. What is Role Level Security (RLS) ?

Ans. It is a policy enforcement mechanism that prevents certain roles from gaining access to specific tuples within specific tables.

51. Which statement is used to manage and modify roles in PostgreSQL ?

Ans. ALTER ROLE statement