Loading…

PostgreSQL Interview Questions and Answers

By Umesh Singh
Last update: 21 Jul 2020, 38 Questions
PostgreSQL Interview Questions

We are going to discuss the most interviewer asked PostgreSQL interview questions for the acknowledgment of future aspirants in this niche. PostgreSQL is one of the most advanced, object-relational database management systems and open-source developed by PostgreSQL Global Development Group at Berkeley Computer Science Department, University of California. With its strong hand on proven data integrity, reliability, architecture, extensibility, and excellent feature set, PostgreSQL has earned the prominence of being a superior and robust solution globally, thus highly increasing the employment base for this niche.

With the operation freedom of all major operating systems including UNIX, Windows, Linux, and Mac OS X; PostgreSQL supports images, text, video as well as sound, and includes programming interfaces for C, C++, Perl, Java, Tcl, Ruby, and Open Database Connectivity.

Benefits of PostgreSQL
  • Enterprise-class functions and performance with an open-source database management system with unlimited development possibilities.
  • Superior development possibility with diverse community purposed modules.
  • Store Procedure functions can be used for a server environment.
  • It offers diverse indexing techniques.
  • Availability of full-text search
  • Diverse extension functions and replications
Quick Questions about PostgreSQL
What is the Latest Version of PostgreSQL? 13.0 released on 21st May 2020
When was PostgreSQL Initial released? 8th July 1996
What language does PostgreSQL use? C Language
Who developed PostgreSQL? Michael Stonebraker
What operating system use PostgreSQL? Windows, Linux, macOS, OpenBSD

Now, let’s discuss an advanced set of PostgreSQL interview questions further above for candidates searching for a near-future job change or going to start their career from the ground level.

Most Frequently Asked PostgreSQL Interview Questions

1. What is PostgreSQL and why it is used for?

It’s a general purposed and advance object-relational database management system used to add custom functions developed using a various programming language such as C, C++, Java, etc. Designed to be extensible, PostgreSQL implements MVCC or multi-version concurrency control.

2. How to select first 10 records in PostgreSQL?

To get the first 10 records from a database in PostgreSQL, use the LIMIT command.

Here is an example of picking the first 10 records from a database called Example:

Example

                                                    

select * from users order by name desc LIMIT 0, 10

3. What is base directory in PostgreSQL? The base directory in PostgreSQL (data_dir/base) is the folder is where PostgreSQL stores all the data you have inserted in your databases. It contains all the sub-directories which are used by a database in your clusters.
4. How to change the datatype of a column in PostgreSQL?

To change the records type of a column, you use the ALTER TABLE assertion as follows:

Example

                                                    

ALTER TABLE users
ALTER COLUMN username[SET DATA] TYPE new_data_type;

5. What is the Maximum size for a table in PostgreSQL?

Even though PostgreSQL has unlimited database size for users, but it has a limit for maximum table size. The maximum table size is set to 32 TB.

Note: This is very essential PostgreSQL interview questions.

6. What are the features of PostgreSQL?

PostgreSQL has many exciting features added to it. Here are a few of them:

  • By protecting data integrity, users can build a fault-tolerant environment.
  • Easy compatibility with significant platforms, languages, and middleware.
  • Multi-version concurrency control is supported.
  • The client-server network architecture is supported.
  • Trigger-based and log-based replication SSL
  • High availability and standby server
7. What is pgAdmin and how do you set up pgAdmin?

PgAdmin is a free, open-source PostgreSQL database administration GUI that is used in Microsoft Windows, Mac OS X, and Linux systems. PgAdmin is used for database server information retrieval, development process, Quality testing, and other ongoing maintenance.

Follow these steps to install PgAdmin:
  • Launch pgAdmin 4.
  • Go to the “Dashboard” tab, click on the “Quick Link” section and then click on “Add new Server”
  • Now, select the “Connection” tab in the “Create-Server” window.
  • Now, configure the connection as follows:
  • Enter your server's IP address in the “Hostname/Address” field.
  • Specify the “Port” as “5432”.
8. What does GEQO stands for in PostgreSQL?

In PostgreSQL, GEQO stands for Genetic Query Optimization. It allows the PostgreSQL search query optimizer to support large joined queries in an effective manner using a non-exhaustive search technique.

9. What is the Maximum size for a database in PostgreSQL?

PostgreSQL has no maximum database size, so users can put unlimited data into it. But, the table, row, and field size are limited. The row and indexes are also universal for its users.

PostgreSQL usually stores its desk facts in chunks of 8KB. The quantity of these blocks is confined to a 32-bit signed integer, giving the most desk dimension of 16TB.

10. Which is better MySQL or PostgreSQL?

In terms of learning databases, MySQL is perfect. It is the first choice for web-based projects merely requiring a database for transactions and nothing else. But, PostgreSQL is better in terms of functionalities and performance. It is more used in the execution of complex queries, data warehousing, and data analysis.

11. What is Multi Version Concurrency Control in PostgreSQL?

Multi-Version Concurrency Control (MVCC) is an advanced method used in PostgreSQL for improving the performance of a database in a multi-user environment. Unlike lock models in other databases, PostgreSQL uses a multi-version environment in which locks that are acquired for reading data don’t conflict with locks acquired for writing the data. Hence, making the process more compartmentalized and a lot faster.

12. Explain PostgreSQL vs. MongoDB?
S.no PostgreSQL MongoDB
1. It’s an object-relational database management system. It uses JSON-like documents to store schema-free data.
2. uses tables, triggers, constraints, roles, stored procedures and views as the core components Uses Key, Collection, Document, and Value.
13. What are the difference between PostgreSQL and Oracle?
S.no Oracle PostgreSQL
1. Mostly aid object-relational database management system open source object-relational database management system
2. The implementation language is C. Implementation language is C and C++.
3. Server operating systems here are OS X, Linux, Windows, z/OS, AIX, and HP-UX. Server operating systems here are HP-UX, NetBSD, Solaris, Windows, Unix, Linux, and FreeBSD.
4. More database productivity Less database productivity
5. Advance security options Good security support but less compared to Oracle
14. How to install PostgreSQL on windows?
  • Download Windows PostgreSQL one-click the installer and run it.
  • Choose to install PostgreSQL as a Windows Service.
  • If needed, change the installation directory and click next
  • Choose the components you want to install & click next
  • If needed, change the data location
  • Enter the user password and if necessary make a note of it.
  • Let the port number by the default
  • Once the installation is complete, uncheck the Stack Builder prompt and click finish
  • Now go to start menu, search and start pgAdmin 4
  • Click on servers present on the pgAdmin homepage and choose Postgre SQL 10
  • Enter the superuser password, and the dashboard will begin for your use
15. How to install PostgreSQL on Ubuntu?

As the default repositories of Ubuntu contain Postgres packages, we can install it easily using the apt packaging system. Refresh the local package index and then install the Postgres packages and a -contrib package which adds additional functionality and utilities. The PostgreSQL software installation process is finished now.

16. List data type in PostgreSQL?

PostgreSQL supports various data types, which includes:

  • Boolean
  • Numeric types
  • Character types
  • Temporal types
  • Array
  • UUID
  • JSON
  • store
  • Special types such as geometric data and particular types
17. What is a child in PostgreSQL?

The ctid field exists in every PostgreSQL table. It is unique for every record in a table and denotes the turple location. It can be used to delete records. The thing to remember, we should only use ctid if we have absolutely no other unique identifier to use.

18. What is table partitioning in PostgreSQL?

In PostgreSQL, table partitioning refers to splitting a large table into smaller sections. PostgreSQL supports list and range partitioning via table heritance. Users have to create each partition as a child table of the master table.

19. How to stats update in PostgreSQL?

An explicit 'vacuum' call is made to update statistics in PostgreSQL. Users can also use the Analyze to perform so.

20. What is indexes in PostgreSQL?

In PostgreSQL, the index is a common way for database performance enhancement. It allows the database server to find the retrieve specific rows faster compared to without index. It also adds overhead to the database system as a whole, so users have to implement them sensibly.

Indexes are special lookup tables that are used by the database search engine to speed up data retrieval. Simply defining, an index is a pointer to a specific data in a table.

21. What types of indexes are supported in PostgreSQL?

PostgreSQL provides several index types like Hash, B-tree, SP-GiST, BRIN, and GIN. All these types uses a separate algorithm that is best suited to queries.

22. How to create a database in PostgreSQL pgAdmin?

Follow these steps to successfully create a database in PostgreSQL using PgAdmin:

  • Log into PostgreSQL using pgAdmin
  • Now, go to the Databases section and right-click on it

create database in postgresql

  • Now, enter the database name, owner and configure parameters and press OK after it is done.

postgresql interview questions

23. What are tokens in PostgreSQL?

A token can be a keyword, an identifier, a quoted identifier, a literal (or constant), or a distinctive personality symbol. Tokens are generally separated with the aid of whitespace (space, tab, newline), however, need not be if there is no ambiguity (which is usually only the case if an exceptional persona is adjoining to some other token type).

24. What is reserved words in PostgreSQL?

Reserved words in PostgreSQL are actually SQL keywords and other symbols having some sort of special meaning when being processed by the Relational Engine.

25. How to change user's password in PostgreSQL?
Follow these steps to carefully change the user password Postgres in Laravel:
  • Step 1: Make yourself the “Postgres” system user (through the root user, sudo or via SSH public key verification)
  • Step 2: Connect to the local server using “PSQL”
  • Step 3: Type this meta-command of PSQL \password

It should look something like this:

postgresql interview questions

26. What is sequence in PostgreSQL?

A sequence in PostgreSQL is a special form of data that is created to generate multiple numeric identifiers in the PostgreSQL database. It is most often used to create sequences and artificial primary keys similar to Auto_Increment in MySQL. The basic role of sequences in PostgreSQL is to create unique identifiers between multiple rows inside a table.

27. What is purpose of overlay function in PostgreSQL?

In PostgreSQL, the Overlay function allows users to replace a substring, which is starting at a specific position and having a specified length.

Here is the syntax for the Overlay Function:

Example

                                                    

overlay(<main_string> placing <replaced_string> <br>
from <starting_position> [ for <number_of_characters>] )

28. How to calculate cube root in PostgreSQL?

To find the cube root of ant given number in PostgreSQL, you can use the CBRT() function.

Example

                                                    

SELECT cbrt(729) AS "Cube Root";
Output - 9

29. What is the operator that is used for case-insensitive regular expression searches in PostgreSQL?

To match a regular expression that is case insensitive, you can use the POSIX regular expression (~*) from the pattern matching operators.

Example

                                                    

'umesh' ~* '.*Umesh.*'

30. What is inverted file in PostgreSQL?

In PostgreSQL, an Inverted file is basically an index data structure used for mapping content to its location to a database file, within a document, or in sets of documents.

It is usually composed of all the distinct words found in a text and a list containing the occurrences of a word in the text.

The inverted file is widely used in a data structure for document retrieval systems in supporting a full-text search.

31. How many byte Unique integers does OIDs in PostgreSQL?

In PostgreSQL, there are 4 bytes of unique integers by default.

32. What is the command that can be used to allocate memory in postgreSQL?

The PostgreSQL functions palloc and malloc are used for allocating memory.

33. What are the ODBC drivers that are available for PostgreSQL?

plsqlODBC is the official PostgreSQL driver. Besides that, there are other third-party ODBC drivers such as Devart, which shall cost you some money.

34. How to check status of PostgreSQL server running or not?

The simplest way to check whether your server in PostgreSQL is running or not:

ps auxwww | grep postgres

In versions other than 8.3 you need to run this command

/Library/PostgreSQL/8.3/bin/postgres -D /Library/PostgreSQL/8.3/data

35. How to enable debug mode in PostgreSQL?

PgAdmin comes with built-in support to debug your Pl/PgSQL codes. However, in order to enable this, you need to compile and install a separate plug-in for PostgreSQL

Now, once you have set up a proper environment for debugging, go to edit /etc/postgresql/9.4/main/postgresql.conf and enable the debugger plugin.

36. How to take the backup of PostgreSQL?

Here are the steps to back up a single database in PostgreSQL. We shall be using the pg_dump tool as it dumps out the content of all the objects in the database to a single file.

Step 1: First of all, navigate to the PostgreSQL BIN folder:

C:\>cd C:\Program Files\PostgreSQL\9.2\bin

Step 2: Now, we shall execute the pg_dump program and use the following method to back up the examples database to the example.tar file to the c:\pgbackup\ folder.

pg_dump -U postgres -W -F t examples > c:\pgbackup\examples.tar

Step 3: Here is your backed up database

c:\pgbackup\dvdrental.tar

37. What is a CTID of PostgreSQL?

In PostgreSQL, the CTID field is one that exists in each and every PostgreSQL table, and it is unique for all records inside a table, which is used to denote the location of the tuples.

38. What are string constants in PostgreSQL?

In PostgreSQL, a string constant is an arbitrary sequence of multiple characters that are bounded by single quotes (').

Example

                                                    

SELECT 'This' 'is' 'an' 'example'

Is equivalent to

SELECT 'This is an example'

Note that this is not the same as using double quotes (").