Postgresql Interview Questions :

In my previous article i have given the idea about different interview questions in sql. In this article i will try to explain Postgresql Interview Questions with its answers.Postgresql which is simply named as Postgres is Widely used Object Relational Database Management System for Large web applications.Here In this article we will discuss History Of Postgresql in detail.In MAC OS Postgresql is default database.Michel Stonebraker is Father of Postgresql who has started Post Ingres project for supporting Contemporary Database systems..Because of its security and open source DBMS it is one of the widely used database management system in world.

Following are some interview questions of postgresql :

1.What is postgresql? Explain history of postgresql. (80% asked Postgresql Interview Questions )

Answer :

Postgresql which is simply named as Postgres is Widely used Object Relational Database Management System for Large web applications.Here In this article we will discuss History Of Postgresql in detail.In MAC OS Postgresql is default database.Michel Stonebraker is Father of Postgresql who has started Post Ingres project for supporting Contemporary Database systems.PostgreSQL’s developers pronounce PostgreSQL as It is abbreviated as Postgres because of ubiquitous support for the SQL Standard among most relational databases.PostgreSQL, originally called Postgres, was created at UCB by a computer science professor named Michael Stonebraker, who went on to become the CTO of Informix Corporation. Stonebraker started Postgres in 1986 as a followup project to its predecessor, Ingres, now owned by Computer Associates. The name Postgres thus plays off of its predecessor (as in “after Ingres”). Ingres, developed from 1977 to 1985, had been an exercise in creating a database system according to classic RDBMS theory. Postgres, developed between 1986-1994, was a project meant to break new ground in database concepts such as exploration of “object relational” technologies.An enterprise class database, PostgreSQL boasts sophisticated features such as Multi-Version Concurrency Control (MVCC), point in time recovery, tablespaces, asynchronous replication, nested transactions (savepoints), online/hot backups, a sophisticated query planner/optimizer, and write ahead logging for fault tolerance.

2.What are different advantages of Postgresql? (80% asked Postgresql Interview Questions )

Answer :

Following are the advantages of postgresql :

• Reliable
• Stable
• Open source
• Extensible
• Easy to learn
• Cross Platform
• Designed for High Volume Environments
• Flexible
• Better Support

3.What are different features of Postgresql?

Answer :

Following are some features of Postgresql :

1) Object relational database
2) Extensibility and support for SQL
3) Database validation and flexible API
4) Procedural languages and MVCC
5) Client server and WAL.

4.What is maximum size of table in postgresql?

Answer:

The postgresql has maximum size of table is 32TB.

5.What is the option that can be used in PostgreSQL to make transactions see rows affected in previous parts of the transaction?(80% asked Postgresql Interview Questions )

Answer:

The SQL standard defines four levels of transaction isolation in terms of three phenomena that must be prevented between concurrent transactions. These undesirable phenomena are:

Dirty read
A transaction reads data written by a concurrent uncommitted transaction.

Nonrepeatable read
A transaction re-reads data it has previously read and finds that data has been modified by another transaction (that committed since the initial read).

Phantom read
A transaction re-executes a query returning a set of rows that satisfy a search condition and finds that the set of rows satisfying the condition has changed due to another recently-committed transaction.

6.Explain Multi Version concurrency control.

Answer :

Multi version concurrency control or MVCC is used to avoid unnecessary locking of the database. This removes the time lag for the user to log into his database. This feature or time lag occurs when some one else is on the content. All the transactions are kept as a record.

7.How the stats updated in Postgresql?

Answer:

14.Explain about functions in Postgresql.

Answer:

Functions are important because they help the code to be executed on the server. Some of the languages which can program functions for efficient use are PL/pgSQL which is the native language of PostgreSQL. Scripting languages are supported by many languages such as PHP, Perl, Python, etc. PL/R a statistical language can also be used.

15.How to find version of postgresql?

Answer:

To find out the version in postgresql use following command :

select version();

16.Which are different type of indexes in postgresql?

Answer:

There are built in functions such as B-tree, hash table, and GIST indices can be used or users can define their own indices. PostgreSQL can scan the index backwards. Expression index could be created with the result of an expression. Partial index created with addition of WHERE clause.

17.How to select first 10 records in postgresql?

Answer:

User needs to use the limit clause to select first n records in postgresql.

Select * from tablename limit 10;

18.How to calculate cube root in postgresql?(60% asked Postgresql Interview Questions )

Answer:

To calculate the Cuberoot use ||/ operator.

Example:

e.g. Select ||/ 16 “CubeRoot of 16”

Result:

CubeRoot of 16
—————-
2.5

19.What are Triggers in postgresql?

Answer:

By SQL query you can trigger an event. Triggers can be activated with the help of INSERT and UPDATE queries. These can be attached to tables. Triggers more than one can be triggered alphabetically. These triggers have the capability to invoke functions from other languages.

20.Which are different datatypes of Postgresql?

Answer:

There are different new datatypes supported by postgresql.Following are those datatypes :

1) Arbitrary precision numeric’s
2) Geometric primitives
3) Arrays
4) XML etc
Users can create their own indexes and make them indexed.

21.Which are different database administration tools used in Postgresql?

Answer :

There are various data administration tools they are

1) Psql
2) Pgadmin
3) Phppgadmin

Most of these tools are front end administration tools and web based interfaces. Out of these phppgadmin is the most popular one.

22.What is pgadmin?Explain.(100% asked Postgresql Interview Questions )

Answer :

Pgadmin forms a graphical front end administration tool. This feature is available under free software released under Artistic License. Pgadmin iii is the new database administration tool released under artistic license.

23.How to create database in postgresql?

Answer:

Creating a database is the primary step in creating a database. A command $createdb newdatabasedb.

CREATE DATABASE

This creates a new database and a message displays CREATE DATABASE which indicates that the creation of the database was successful.

24.What is Write ahead log in postgresql?

Answer:

write-ahead log (WAL),  means it always writes the transactions to the log following with writing the modified pages to the disk to maintain the transaction ACID properties.

25.Explain about tokens in postgresql?

Answer :

Tokens are also known to contain several special character symbols. It can be considered as keyword, constant, identifier and quoted identifier. Keywords include pre defined SQL meanings and SQL commands. Variable names such as tables, columns, etc are represented by identifiers.

These are some bery important postgresql interview questions.Hope you like this article of Postgresql Interview Questions and dont forget to comment in comment section.These postgresql interview questions are really helpful to users.