PostgreSQL Interview Questions and Answers [ SOLVED ]

1) What is PostgreSQL?

Ans:

PostgreSQL is a famous Object-relational database management system. It is an open-source platform that is used for create advanced applications. Familiarity with a Linux and UNIX can be an additional benefit when running PostgreSQL.

2) List some PostgreSQL features.

Ans:

• Data integrity protection helps to users create a fault-tolerant environment.
• Easily compatible with the important platforms, middleware and languages.
• It supports a Multi-version concurrency control.
• It supports a customer-server network architecture.
• Log-based and Trigger-based on replication SSL.
• Highly available and standby a server

3) List different data types in PostgreSQL.

Ans:

• UUID
• Numeric types
• Temporal types
• Geometric primitives
• Arbitrary precision numeric
• XML
• Boolean
• Character types
• Array
• JSON

4) List some advantages and disadvantages of a PostgreSQL.

Ans:

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 a MySQL in terms of performance.
• It does not have support for more open source applications in comparison with MySQL.
• With a greater focus on a compatibility, modifications to enhance speed require additional work.

5) What are a string Constants?

Ans:

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

Example: ‘It is an example for string constant’.

6) What is a Multi-Version Control?

Ans:

Multi-Version Concurrency Control in a PostgreSQL is used to prevent unnecessary database locking. It eliminates a delay time for the user logging into their database. It occurred when some other people are accessing a content. All these transactions are to be captured.

7) What are indices?

Ans:

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

8) What are tokens?

Ans:

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

9) What is a table partitioning?

Ans:

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

10) How can start and stop a database server on PostgreSQL? And how can check a whether PostgreSQL is up and running?

Ans:

Prior to accessing the database, should be able to start a database server. The database server program is referred to as a Postgres. The Postgres program needs to know where to look for data it should use. This is finished using the -D option. So the easiest way of starting a server is:

11) How is pgadmin used in a PostgreSQL?

Ans:

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

12) Compare a PostgreSQL with MySQL.

Ans:

MySQL is the “user-friendly, web developer” database, while PostgreSQL is a “feature-rich and standards-compliant” database. PostgreSQL is freely licensed and part of community; MySQL is GPL licensed and belongs to an Oracle. Beyond this, every user of database should make their own assessment; free software facilitates to comparisons.

13) How can change the column data type in a PostgreSQL?

Ans:

Using ALTER TABLE command, can change a column type.

For example:

14) How are statistics updated within a Postgresql?

Ans:

In PostgreSQL, To update a statistics, a special function known as an explicit “vacuum” call is done. The way to do this is tomake a Vacuum where the Analyse option is used to update a stats in the Postgresql.

15) What is enable-debug command in a PostgreSQL?

Ans:

The enable-debug command allows to compile all apps and libraries. Executing this process usually prevents a system, but it also increases the size of a binary file. The debug symbols that are present in usually help developers identify bugs and other problems that may occur related to their script.

16) Which option can be used in a PostgreSQL to view the affected rows in an earlier parts of the transaction?

Ans:

The SQL standard is explained by four levels of isolating transactions concerning essentially three phenomena. The three phenomena should be avoided between a concurrent transactions. Unwanted phenomena include:

Non-repeatable read: A transaction that will read a data which is already read and then discovers that the data has already been altered by a another transaction.

Phantom read: A transaction that will execute query again by returning a set of rows that meets search condition and finds the set of rows which satisfied a requirements that have changed as a result of a another recently initiated transaction.

Dirty read: A transaction that reads a data which is written by simultaneous uncommitted transaction is called the dirty read.

17) Briefly describe functions of Postgresql.

Ans:

Functions are an important part of running the code on a server everywhere. Some languages used for the programming functions are PL/pgSQL, a native PostgreSQL language, as well as other scripting languages like Perl, PHP, Python, etc. A statistical language is called PL/R can also be used to make a functions high efficient.

18) What are the various improvements in the relational data model through PostgreSQL?

Ans:

A number of improvements have been made to a simple relationship data model through a PostgreSQL. They support an arrays that include various values, functions, inheritance, and extensibility. Jargon is various in its object-oriented nature in which tables are known as classes.

19) What kind of database administration tools do use in a Postgresql?

Ans:

Variety of data administration tools. They are Pgadmin, Psql, Phppgadmin. Phppgadmin is the most famous among them. Most of these tools are web-based on interfaces and front-end administration tools.

20) What is Ctid?

Ans:

CTIDs is a field that exists in all the PostgreSQL tables and is known to a recognize specific physical rows based on their block as well as offset positions in a specific table. They are used for index entries for specifying a physical rows. It is unique for every record in a table and easily indicates the position of tuple. The CTID of a logical row changes at time of its update, such that the CTID will not be used long-term row identifier. it may be helpful to identify line within a transaction where no concurrent updates are be expected.

21) Explain about a write-ahead logging.

Ans:

Write-ahead logging is a standard way of ensuring a data integrity. It is a correct rule or protocol for writing the actions and modifications in a transaction log. This feature enhances a reliability of the database by saving a modifications before any modification or update of the database. It provides a database log if the database crashes. It allows work to begin from a moment it was interrupted.

22) When should PostgreSQL be avoided?

Ans:

At the cost of speed, PostgreSQL was built to be an expandable and compatible. PostgreSQL may not suit if a project needs the fastest read operations possible.

Due to its wide range of features and its more adherence to a SQL standard, Postgres may be excessive for simple database configurations. MySQL is the practical choice for an operations like read-heavy where need speed.

While PostgreSQL provides a solid support for replication, it is always a relatively new functionality, and certain configurations like primary architecture are only possible with expansions. Replication is a advanced feature of MySQL. Most users feel that a replicating MySQL is easier to implement, especially for those without required experience in administering databases and systems.

23) What do mean by a Full-Text search in PostgreSQL?

Ans:

This refers to the technique or method of searching for single or a collection of documents stored on the computer in a full-text database. It may be simply distinguished from searches according to the metadata or portions of the original texts depicted in a databases. While PostgreSQL is not as advanced as a SOLR and Elasticsearch, these two tools are specific to a full-text search.

24) What are BRIN indices?

Ans:

Once the BRIN index has been installed, PostgreSQL will be able to select a minimum and maximum values by reading the selected column for each 8k page of a data saved. After that, PostgreSQL will save the page number, the maximum and minimum values of a selected column in the BRIN indexes.

25) What is Parallel Query?

Ans:

A parallel query is a PostgreSQL feature in which it can design a query plans that can leverage number of CPUs to respond more quickly to a queries.

26) What are GiST indexes in PostgreSQL?

Ans:

The giST is an extensible for data structure which enables users to develop an indexes on all data types. It is also responsible for all lookups on data. The giST is capable of doing so by inserting an API into a Postgres index system.

27) What is the Atomicity property’s function in a PostgreSQL?

Ans:

The Atomicity property guarantees a success of all operations within a one work unit.

28) Does Postgresql be executed on Cloud?

Ans:

Yes. Like the other open source databases, PostgreSQL can be easily executed in a virtual containers and is more portable. Many companies such as GoGrid, Heroku and Joyent have PostgreSQL support within Cloud hosting environments.

29) How can perform queries through multiple databases?

Ans:

There is no better way to query a database other than a present one. As PostgreSQL loads database-specific system catalogs don’t know how a cross-database query should be behave. contrib/dblink enables cross-database queries with function calls. Of course, a customer can also establish a concurrent connections to different databases and merge client-side results.

30) Can create a shared storage PostgreSQL server cluster?

Ans:

PostgreSQL will not support clustering by using shared storage on aSCSI, SAN, iSCSI volume, or other shared media. Such as “RAC-style” clustering is not supported. Only the replicated clustering is supported present.

31. What is a write ahead logging?

Ans:

Postgres’ write ahead logging increases a reliability of the db by logging changes before any changes or updates to database. This provides a log of database in case of db crash and this also helps to start a work from point it was discontinued. This it is useful for a crash recovery.

32. What is meant by a Partitioned table in PostgreSQL?

Ans:

In PostgreSQL, the partitioned table is a logical structure and a special table divided into a parts. It allows a splitting of large size tables into smaller ones, known as a partitions.

33. Define a purpose of pgAdmin in the PostgreSQL server.

Ans:

In PostgreSQL, pgAdmin is a data administration tool that helps to manage and observe more PostgreSQL and EDB advanced server database servers. It is useful for a recovering, testing, managing, and developing databases.

34. Name the process of dividing a large table into a smaller sizes in PostgreSQL.

Ans:

The process is known as “table partitioning”.

35. How to avoid needless database locking in a PostgreSQL?

Ans:

To avoid the needless or unnecessary locking of database, can used a MVCC (Multi-version concurrency control).

36.What is TRUNCATE used for?

Ans:

TRUNCATE TABLE statement is used to remove all the data quickly and an efficiently from the table.

37. Define a process of deleting database in PostgreSQL.

Ans:

• Can used a SQL command- DROP DATABASE.
• Also, can use”dropdb” which is feasible command-line .

38. What can find in the Schema?

Ans:

A schema in PostgreSQL includes the : views, indexes, operators, sequences, data types, tables, and functions.

39. Define the methods that PostgreSQL provides to build new database.

Ans:

• Using an SQL command – CREATE DATABASE to create database .
• Also, can use ‘created’ which is executable command-line .

40. Name different types of operators in PostgreSQL.

Ans:

Arithmetic operators, Logical operators, Comparison operators, Bitwise operators.

41. What is another name for a database callback functions? What is their purpose in PostgreSQL?

Ans:

The PostgreSQL Triggers are also known as a database callback functions. When a explained database event takes place, PostgreSQL Triggers are automatically executed or cited.

42. For what do use indexes in PostgreSQL?

Ans:

To speed up a data recovery in PostgreSQL, search engine uses indexes.

43. Define the use of Cluster Index.

Ans:

The Cluster index classifies the table data rows on a basis of their key values. the order of the data rows within a data pages communicates with data rows’ order in the index.

44. Define the benefits of specifying data types in columns when create a table.

Ans:

The advantages of specifying a data types in columns include consistency, conciseness, validation/confirmation, and performance.

45. Explain how to update a statistics in PostgreSQL.

Ans:

Use a special function “vacuum” in order to update a statistics in the PostgreSQL database.

46. Define the method to remove complete data from existing table.

Ans:

Completely remove data from a current table through PostgreSQL TRUNCATE TABLE command.

47. Name the different properties of a transaction in PostgreSQL and their Acronym.

Ans:

The transaction properties in a PostgreSQL consist of Atomicity, Consistency, Isolation, and Durability and they are collectively called ACID properties.

48. What is the use of CTIDs field?

Ans:

The CTIDs field helps to locate a certain physical rows within a table as per their block and balances a positions in that table.

49. Name the commands to control transactions in a PostgreSQL.

Ans:

The commands for Transaction Control in a PostgreSQL include the following- BEGIN, COMMIT, TRANSACTION, and ROLLBACK.

50. Distinguish between a SQL and PostgreSQL?

Ans:

• Cannot update the views in a PostgreSQL database like in SQL.
• In PostgreSQL, there is no need to create a DLL (dynamic link library) to see code’s activity like in a SQL database.
• Moreover, SQL offers a computed columns but PostgreSQL doesn’t offer any.
• SQL doesn’t support dynamic actions but a PostgreSQL supports them.

51. Define the security measures in a Postgre

Ans:

PostgreSQL database uses a SL connections that help to encrypt client or server reports to an ensure security services.

52. What is the process of splitting a large table into a smaller pieces called in PostgreSQL?

Ans:

It is called a table partitioning.

53. What purpose does a pgAdmin in PostgreSQL server?

Ans:

The pgAdmin in a PostgreSQL is data administration tool. It serves a purpose of retrieving, developing, testing, and maintaining databases.

54. How can avoid unnecessary locking of database?

Ans:

Use MVCC (Multi-version concurrency control) to avoid unnecessary locking of database.

55. What does a schema contain?

Ans:

A schema contains tables along with a data types, views, indexes, operators, sequences, and functions.

56. How to convert a role to user in a PostgreSQL?

Ans:

• Login role to the target role to convert it to a user.
• Alter role with login.

57. What indexes are used?

Ans:

Indexes are used by a search engine to speed up a data retrieval.

58. What does Cluster index do?

Ans:

Cluster index sorts a table data rows based on their key values.

59. What are the benefits of specifying a data types in columns while creating a table?

Ans:

Some of these benefits are include consistency, compactness, validation, and performance.

60. What do need to do to update a statistics in PostgreSQL?

Ans:

To update a statistics in PostgreSQL, need to used a special function called a vacuum.

61. What is the disadvantage of DROP TABLE command in a deleting complete data from an existing table?

Ans:

Though the DROP TABLE command has the ability to delete a complete data from an existing table, the disadvantage with it is – it removes complete table structure from database. Due to this, need to re-create a table to save data.

62. What are the various properties of a transaction in PostgreSQL? Which acronym is used to refer to them?

Ans:

The properties of a transaction in a PostgreSQL include Atomicity, Consistency, Isolation, and Durability. These are referred to by a acronym, namely ACID.

63. What purpose does a CTIDs field serve?

Ans:

The CTIDs field identifies a specific physical rows in table according to their block and offsets a positions in that table.

64. Which are the commands used to control a transactions in PostgreSQL?

Ans:

The commands used to control transactions in a PostgreSQL are BEGIN TRANSACTION, COMMIT, and ROLLBACK.

65. How is security ensured in a PostgreSQL?

Ans:

PostgreSQL used a SSL connections to encrypt client or server communications so that security will be ensured.

66. What is the function of Atomicity property in a PostgreSQL?

Ans:

Atomicity property ensures the successful completion of all operations in work unit.

67. What does a Write-Ahead Logging do?

Ans:

The Write-Ahead Logging enhances a database reliability by a logging changes before any changes or updates are made a database.

68. What are some of important data administration tools supported by a PostgreSQL?

Ans:

Some of important data administration tools supported by a PostgreSQL are Psql, Pgadmin, and Phppgadmin.

69. How can save the binary data in PostgreSQL?

Ans:

Can save the binary data in PostgreSQL either by using bytes or by using large object feature.

70. What is non-clustered index?

Ans:

In a non-clustered index, the index rows order doesn’t match a order in an actual data.

71. What is the purpose of table space in a PostgreSQL?

Ans:

It is a location in a disk. PostgreSQL saves the data files, which contain indices and tables, etc.

72. What does a token representation in SQL Statement?

Ans:

In a SQL Statement, a token represents a identifier, keyword, quoted identifier, special character symbol, or constant.

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

Ans:

Returns Cube Root of number

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

74. Provide a brief explanation of functions in PostgreSQL.

Ans:

Functions are essential because they help to execute the code on the server. Some of the languages to program functions are PL/pgSQL, a native language of PostgreSQL, and other scripting languages such as Perl, Python, PHP, etc. The statistical language PL/R can also be used to increase a efficiency of functions.

75. Can explain Pgadmin?

Ans:

Pgadmin is a feature known to form of graphical front-end administration tool. This feature is available for under free software released under an Artistic License. Pgadmin iii is a new database administration tool released under an artistic license.

76. What will be new characteristics of a PostgreSQL 9.1?

Ans:

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

77. What is a command enable-debug?

Ans:

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

78. What are the indices of a PostgreSQL?

Ans:

These are inbuilt functions or methods such as GIST Indices, hash table, and B-tree, which the user can use to scan an index backwardly. Users can also explain their indices of a PostgreSQL.

79. What is the option in a PostgreSQL to check rows affected in the previous part of the transaction?

Ans:

The SQL standard is explaioned by four transaction isolation levels regarding three phenomena. The three phenomena must be prevented between a concurrent transactions. The unwanted phenomenon is:

Phantom read: A transaction that re-executes a query, returning a set of rows that satisfy the search condition and then finds that the location of rows that have been helping a situation 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 an another transaction has already modified data.

Dirty reads: A transaction that reads data written by the concurrent uncommitted transaction is a dirty read.

80. What is the purpose of an Array_To_String in PostgreSQL?

Ans:

The Array_To_String function concatenates an array elements by using a provided delimiter.

81. How to select a first 10 records in PostgreSQL?

Ans:

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

82. What is a base directory in PostgreSQL?

Ans:

The base directory in a PostgreSQL (data_dir/base) is the folder is where PostgreSQL saves all the data that have inserted in a databases. It contains all the sub-directories which are used by database in a clusters.

83. What does GEQO stands for PostgreSQL?

Ans:

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

84. Explain PostgreSQL vs. MongoDB?

Ans:

PostgreSQL	MongoDB
It’s object-relational database management system.	It used a JSON-like documents to store schema-free data.
Uses tables, triggers, constraints, roles, stored procedures and views as a core components.	Used Key, Collection, Document, and Value.

85. What are difference between PostgreSQL and Oracle?

Ans:

Property	PostgreSQL
Mostly aided object-relational database management system.	An open source object-relational database management system.
Implementation language is C.	Implementation language is a C and C++.
Server operating systems are OS X, Linux, Windows, z/OS, AIX, and HP-UX.	Server operating systems are HP-UX, NetBSD, Solaris, Windows, Unix, Linux, and FreeBSD.
A More database productivity.	A Less database productivity.
Advance security options.	Good security support but less compared to Oracle.

86. How to install a PostgreSQL on Ubuntu?

Ans:

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

87. What is child in PostgreSQL?

Ans:

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

88. How to stats update in a PostgreSQL?

Ans:

An explicit ‘vacuum’ call is made to update a statistics in PostgreSQL. Users can also used the Analyze to perform so.

89. What is a reserved words in PostgreSQL?

Ans:

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

90. What is a sequence in PostgreSQL?

Ans:

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

91. What is purpose of an overlay function in PostgreSQL?

Ans:

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

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

Ans:

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

93. What is inverted file in a PostgreSQL?

Ans:

• In PostgreSQL, an Inverted file is basically an index data structure used for a mapping content to its location to a database file, within a document, or in sets of a documents.
• It is usually composed of all the distinct words found in the text and a list containing the occurrences of a word in a text.
• The inverted file is widely used in data structure for a document retrieval systems in supporting full-text search.

94. How many byte a Unique integers does OIDs in a PostgreSQL?

Ans:

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

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

Ans:

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

96. What are the ODBC drivers that are available for a PostgreSQL?

Ans:

PlsqlODBC is a official PostgreSQL driver. there are other third-party ODBC drivers like Devart, which shall cost some money.

97. How to enable a debug mode in a PostgreSQL?

Ans:

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

98. What are Benefits of a PostgreSQL?

Ans:

• Enterprise-class functions and performance with an open-source database management system with a unlimited development possibilities.
• Superior development possibility with a diverse community purposed modules.
• Save a Procedure functions can be used for the server environment.
• It offers a diverse indexing techniques.
• Availability of a full-text search.
• Diverse extension functions and also replications.

99.Who is a PostgreSQL Global Development Group?

Ans:

The “PGDG” is an international, unincorporated association of individuals and companies who have a contributed to PostgreSQL project. The PostgreSQL Core Team generally act as a spokespeople for a PGDG.

100.Who is a PostgreSQL Core Team?

Ans:

A committee of five to seven senior contributors to a PostgreSQL who do the following for a project: (a) set release dates, (b) handle confidential matters for a project, (c) act as spokespeople fora PGDG when required, and (d) arbitrate a community decisions which are not settled by a consensus.