PostgreSQL Interview Questions

                     Top 20 PostgreSQL Interview Questions

1) What is PostgreSQL?
Postgres or simply known as Postgresql in the SQL world is one of the widely and popularly used for Object-Relational Database Management Systems that is used mainly in large web applications. It is one of the open-source object-relational database systems which also powerful. It provides additional and substantial power by incorporating four basic concepts in such a way that the user can extend the system without any problem. It extends and uses the SQL language that is combined with various features to safely scale and store the intricate data workloads.
 
2) List some of the features of Postgresql?
Following are some of the major features of Postgresql :
1. Object-relational database
2. Supports major Operating systems
3. Support Extensibility for SQL and Complex SQL queries
4. Nested transactions
5. Flexible API and Database validation
6. Multi-version concurrency control (MVCC) and Procedural languages
7. WAL and Client-server
8. Table inheritance & Asynchronous replication

3) List different datatypes of Postgresql?
There are new, different data types supported by Postgresql. Following are those data types:
• UUID
• Numeric types
• Boolean
• Character types
• Temporal types
• Geometric primitives
• Arbitrary precision numeric
• XML
• Arrays etc.
Users can also create their indexes and get them indexed.

4) List different advantages of Postgresql?
Following are some of the advantages of PostgreSQL :
• Stable
• Reliable
• Extensible
• Easy to learn
• Open source
• Designed for High Volume Environments
• Cross-Platform
• Better Support
• Flexible

5) What are string constants in PostgreSQL?
A string constant in PostgreSQL is a sequence of some character that is bounded by single quotes (').
Example: 'This is a string Constant'

6) What is multi-version control in PostgreSQL?
Multi-version concurrency control or MVCC in PostgreSQL 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 the transactions are kept as a record.

7) What are the Indices of PostgreSQL?
Indices of PostgreSQL are inbuilt functions or methods like GIST Indices, hash table, and B-tree (Binary tree) which can be used by the user to scan the index in a backward manner. Users can also define their indices of PostgreSQL.

8) What are tokens in PostgreSQL?
Tokens in PostgreSQL are the building blocks of any source code. They are known to comprise many of the special character symbols. These can be regarded as constants, quoted identifiers, other identifiers, and keywords. Tokens which are keywords consist of pre-defined SQL commands and meanings. Identifiers are used to represent variable names like columns, tables, etc.

9) What is table partitioning in PostgreSQL?
Table partitioning in PostgreSQL is the process of splitting a large table into smaller pieces. A partitioned table is a logical structure used to divide a large table into smaller pieces called partitions.

10) How to start a database server in PostgreSQL?
Before you can have access to the database, you must be able to start the database server. The server program of the database is called Postgres. The Postgres program must know where to find the data it is supposed to use. This is done with the -D option. Thus, the simplest way to start the server is:
1. /usr/local/etc/rc.d/010.pgsql.sh start
2. /usr/local/etc/rc.d/PostgreSQL start

11) What is the use of pgadmin in PostgreSQL?
It is a free open source GUI tool PostgreSQL database administration tool for Windows, Mac OS X, and Linux systems. It is used for information retrieval, development, testing, and ongoing maintenance of Databases.

12) What is Cube Root Operator (||/) in PostgreSQL?
PostgreSQL Cube Root Operator (||/) is used to get the cube root of a number.
Example: SELECT ||/40 AS "Cube Root of 40";

13) How can we change the column's datatype in PostgreSQL?
Use change column type statement with ALTER TABLE command to change a column type in PostgreSQL.
Example: ALTER TABLE table_name
ALTER COLUMN column_name [SET DATA] TYPE new_data_type;

14) How are the stats updated in Postgresql?
For statistics update in PostgreSQL, a special function called explicit ‘vacuum’ call is made. The method to do is to create a Vacuum where the option of Analyze is used to update statistics in Postgresql
VACUUM ANALYZE; is the syntax.

15) Compare ‘PostgreSQL’ with ‘NoSQL’
The expression ‘NoSQL’ encompasses a wide collection of implementations that are part of the non-relational database. This includes tiny embedded databases such as TokyoCabinet, massive bunched data processing platforms such as Hadoop, and everything in between. In short, it’s practically impossible to comment on the range comprised by NoSQL as a typical class.
Choosing between the non-relational and relational databases is also quite commonly debated as both have existed alongside each other for over forty years. In fact, users should opt for the features, community support, and implementation of the database according to their current application needs. Additionally, the use of multiple various databases for sizeable projects is becoming more of a norm than a trend. Moreover, the users of PostgreSQL are no exception.

16)  What will be the new characteristics of Postgre 9.1?
During the process of updating the project, one can never be certain what 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 which include JSON support, synchronous replication, nearest-neighbor geographic searches, collations 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.

17)  What is the command enable-debug in PostgreSQL?
The command enable-debug is used to enable the compilation of all the applications and libraries. The execution of this procedure usually impedes the system, but it also amplifies the binary file size. Debugging symbols that are present generally assist the developers in spotting the bugs and other problems which may arise associated with their script. 

18) What is the option that can be used in PostgreSQL to make transactions see rows affected in previous parts of the transaction?
 The SQL standard is defined by four levels of transaction isolation basically regarding three phenomena. The three phenomena must be prevented between concurrent transactions. The unwanted phenomena are:
• Phantom read: A transaction that re-executes a query, returning a set of rows that satisfy a search condition and then finds that the set of rows that have been satisfying the condition has changed due to another recently-committed transaction.
• Non-repeatable read: A transaction that re-reads the data that it has previously read and then finds that data has already been modified by another transaction (that committed since the initial read).
• Dirty read : A transaction when reads data that is written by a concurrent uncommitted transaction is the dirty read.
 
19) What is Multi-Version concurrency control?
MVCC or better known as Multi-version concurrency control is used to avoid unwanted locking of the database. The time lag for the user is removed so that one can easily log into his database. All the transactions are well- kept as a record. The time lag occurs when someone else is on the content.

20) Provide a brief explanation of the functions in Postgresql.
Anywhere, 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. statistical language named PL/R can also be used to increase the efficiency of the functions.