Interview questions for Postgre SQL

1. What are the main features of PostgreSQL?

Following are some of the features of PostgreSQL
• Object-relational database.
• Support and extensibility for SQL.
• Flexible API and database validation.
• MVCC and procedural languages,
• WAL and Client-Server.

2. What is Cube Root Operator (||/) in PostgreSQL?

Returns Cube Root of a number
e.g. Select ||/ 16 “Cube Root of 16”

3. Provide a brief explanation of the functions in PostgreSQL?

Functions are an important part because they help in executing the code on the server. Some of the languages to program functions are PL/pgSQL, a native language of PostgreSQL, and other scripting languages like Perl, Python, PHP, etc. The statistical language named PL/R can also be used to increase the efficiency of the functions.
Let us move to the next PostgreSQL Interview Questions.

4. Can you explain pgadmin?

Pgadmin is a feature that is known to form 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.

5. What is Multi-version control?

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 someone else is accessing the content. All transactions are kept as a record.

6. What will be the new characteristics of PostgreSQL 9.1?

During the process of updating the project, one can never be certain that features will go in and which ones won’t make the cut. The project has precise and stringent standards for quality, and some patches may or may not match them before the set deadline. Currently, the 9.1 version is working on some important features, including JSON support, synchronous replication, nearest-neighbour geographic searches, collation at the column level, SQL/MED external data connections, security labels as well as index-only access. However, this list has a high chance of changing completely by the time Postgre 9.1 is released.

7. List various data types in PostgreSQL?

PostgreSQL supports various data types. Some of them are:
• UUID
• Numeric types
• Temporal types
• Geometric primitives
• Arbitrary precision numeric
• XML
• Boolean
• Character types
• Array
• JSON

8. List some advantages and disadvantages of PostgreSQL?

Advantages of PostgreSQL:
• Reliable
• Flexible
• Stable
• Easy to learn
• Extensible
• Cross-Platform
• Open-source
• Better Support
• Designed for High Volume Environments
Disadvantages of PostgreSQL:
• It is slower compared to MySQL in terms of performance.
• It does not have support for many open source applications in comparison with MySQL.
• With a greater focus on compatibility, modifications to enhance speed require additional work.

9. What are string Constants?

The string constant consists of a sequence of characters tied by single quotes(‘). It is used when inserting a character or passing a character to database objects. PostgreSQL enables the use of single quotes but is integrated by a C-style backslash. It is used in data parsing.
Example: ‘It is an example for a string constant’.

10. What is Multi-Version Control?

Multi-Version Concurrency Control in PostgreSQL is used to prevent unnecessary database locking. It eliminates the delay time for the user logging into their database. It occurs when some other people are accessing the content. All these transactions are captured.
If you want to Explore more about PostgreSQL? then read our updated article – PostgreSQL Tutorial

11. What are the indices?

PostgreSQL offers a number of index types: Hash, B-tree, GiST, SP-GiST, BRIN and GIN. Each type of index will use a different algorithm which is most suitable for different types of queries. CREATE INDEX will create B-tree indexes by default that match the most popular situations. Users can set their PostgreSQL indexes as well.

12. What does a token represent in an SQL Statement?

A token represents an identifier, keyword, quoted identifier, special character symbol, or a constant in a SQL Statement.

13.  What do you understand by inverted file in PostgreSQL?

In PostgreSQL, an inverted file is an index data structure used to map content to its location to a database file, within a document, or in sets of documents. It generally includes the distinct words found in a text and a list containing the occurrences of a word in the text. It is used in a data structure for document retrieval systems to provide a full-text search.

14. What are tokens?

PostgreSQL tokens are the building blocks for any source code. They include a lot of special character symbols. A token represents an identifier, a quoted identifier, a keyword, a special character symbol or a literal symbol.

15. What is table partitioning?

In PostgreSQL, table partitioning is a process in which a large table is divided into smaller pieces called partitions. PostgreSQL supports ranges and lists partitioning through table inheritance. Users must create every partition like a child table in the main table.

16. What are some new characteristics introduced in Postgre?

The new PostgreSQL 9.1 version is working on important features such as JSON support, synchronous replication, nearest-neighbor geographic searches, SQL/MED external data connections, security labels, and index-only access.
Following is a list of some newly added characteristics in PostgreSQL 9.1:
o Added support for foreign tables.
o Added support for per-column collation.
o Added some extensions to simplify packaging of additions to PostgreSQL.
o Added a true serializable isolation level.
o Added nearest-neighbor (order-by-operator) searching to GiST indexes.
o Added a SECURITY LABEL command and support for SELinux permissions control.

17. How can we start and stop a database server on PostgreSQL? And how can we check whether PostgreSQL is up and running?

Prior to accessing the database, you should be able to start the database server. The database server program is referred to as Postgres. The Postgres program needs to know where to look for the data it should use. This is accomplished using the -D option. So the easiest way of starting the server is:
• usr/local/etc/rc.d/010.pgsql.sh start
• /usr/local/etc/rc.d/postgresql start
We use the following way to stop the server:
• /usr/local/etc/rc.d/010.pgsql.sh stop
• /usr/local/etc/rc.d/postgresql stop
We can verify if the postgresql server is up and running by:
• /usr/local/etc/rc.d/010.pgsql.sh status
• /usr/local/etc/rc.d/postgresql status?

18. How is pgadmin used in PostgreSQL?

Pgadmin is a well-known feature for forming a front-end graphic administration tool. This functionality is available as free software with an artistic license. The latest database administration tool available with an artistic license is Pgadmin iii. It is used to retrieve information, develop, test and continuous maintenance of databases.