PostgreSQL Interview Questions and Answers [ SOLVED ]

Last updated on 10th Nov 2021, Blog, Interview Questions

These TypeScript Interview Questions have been designed specially to get you acquainted with the nature of questions you may encounter during your interview for the subject of TypeScript . As per my experience good interviewers hardly plan to ask any particular question during your interview, normally questions start with some basic concept of the subject and later they continue based on further discussion and what you answer.we are going to cover top 100 TypeScript  Interview questions along with their detailed answers. We will be covering TypeScript  scenario based interview questions, TypeScript  interview questions for freshers as well as TypeScript  interview questions and answers for experienced.

1) What is PostgreSQL?

Ans:

Postgres or simply known as Postgresql in the SQL world is one of the widely and popularly used for Object-Relational Database Management System 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 ?

Ans:

Following are some of the major features of Postgresql :

• Object-relational database
• Supports major Operating systems
• Support Extensibility for SQL and Complex SQL queries
• Nested transactions
• Flexible API and Database validation
• Multi-version concurrency control (MVCC) and Procedural languages
• WAL and Client server
• Table inheritance & Asynchronous replication

3) List different datatypes of Postgresql?

Ans:

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.

4) List different advantages of Postgresql?

Ans:

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?

Ans:

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?

Ans:

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?

Ans:

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?

Ans:

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?

Ans:

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 database server in PostgreSQL?

Ans:

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:

• /usr/local/etc/rc.d/010.pgsql.sh start
• /usr/local/etc/rc.d/PostgreSQL start

11) What is use of pgadmin in PostgreSQL?

Ans:

It is a free open source GUI tool PostgreSQL database administration tool for Windows, Mac OS X, and Linux system. It is used for information retrieval, development, testing, and ongoing maintenance of Databases.

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

Ans:

PostgreSQL Cube Root Operator (||/) is used to get the cube root of a number.

Example:

SELECT ||/40 AS “Cube Root of 40”;

13)Shows the PostgreSQL Architecture?

Ans:

14) How are the stats updated in Postgresql?

Ans:

It is not that hard as it seems. To get your statistics updated 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’

Ans:

The expression ‘NoSQL’ encompasses a wide collection of implementations which 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, 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?

Ans:

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) Explain the history of PostgreSQL.

Ans:

The origin of PostgreSQL dates back to 1986 as part of the POSTGRES project at the University of California at Berkeley and has more than 30 years of active development on the core platform. It runs on all the major operating systems and has been ACID-compliant since 2001. It also has add-on like PostGIS database extender.

In MAC OS Postgresql is the default database. Michel Stonebraker is Father of Postgresql who has started the 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.

18) What is the command enable-debug in PostgreSQL?

Ans:

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 which are present generally assist the developers for spotting the bugs and other problems which may arise associated with their script.

19) What is the option that can be used in PostgreSQL to make transactions see rows affected in previous parts of the transaction?

Ans:

The SQL standard is defined by four levels of transaction isolation basically regarding three phenomena. The three phenomenon 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.

20) Put some light on Multi-Version concurrency control?

Ans:

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.

21) Provide a brief explanation of the functions in Postgresql.

Ans:

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.

22) Which are different types of database administration tools used in Postgresql?

Ans:

There are the number of data administration tools, and they are

• Phppgadmin
• Psql
• Pgadmin

23) Do provide an explanation for pgadmin?

Ans:

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.

24) What Is A Ctid?

Ans:

CTIDs is a field, which exists in every PostgreSQL table and is known to identify specific physical rows according to their block and offset positions within a particular table. They are used by index entries to point to physical rows. It is unique for each record in the table and easily denotes the location of a tuple. A logical row’s CTID changes when it is updated, so the CTID cannot be used as a long-term row identifier. However, it is sometimes useful to identify a row within a transaction when no competing update is expected.

25)Explain the Delphix Architecture with PostgreSQL?

Ans:

26) What is difference between clustered index and non clustered index?

Ans:

Difference between clustered index and non clustered index

• Cluster index is an index type that is used to sort table data rows on the basis of their key values. In RDBMS primary key allows us to create a clustered index based on that specific column.
• A non-clustered index (or regular b-tree index) is an index where the order of the rows does not match the physical order of the actual data. It is instead ordered by the columns that make up the index.

27) How much will you rate yourself in PostgreSQL?

Ans:

When you attend an interview, Interviewer may ask you to rate yourself in a specific Technology like PostgreSQL, So It’s depend on your knowledge and work experience in PostgreSQL.

28) What challenges did you face while working on PostgreSQL?

Ans:

This question may be specific to your technology and completely depends on your past work experience. So you need to just explain the challenges you faced related to PostgreSQL in your Project.

29) What was your role in the last Project related to PostgreSQL?

It’s based on your role and responsibilities assigned to you and what functionality you implemented using PostgreSQL in your project. This question is generally asked in every interview.

30) How much experience do you have in PostgreSQL?

Ans:

Here you can tell about your overall work experience on PostgreSQL.

31)Have you done any PostgreSQL Certification or Training?

Ans:

It depends on the candidate whether you have done any PostgreSQL training or certification. Certifications or training are not essential but good to have.

32). Explain About Write Ahead Logging?

Ans:

This feature increases the reliability of the database by logging changes before any changes or updations to the data base. This provides log of database incase of a database crash. This helps to start the work from the point it was discontinued.

33) Explain About Multi Version Concurrency Control?

Ans:

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.

34) How To Start The Database Server?

Ans:

• /usr/local/etc/rc.d/010.pgsql.sh start
• /usr/local/etc/rc.d/postgresql start

35) How To Stop The Database Server?

Ans:

• /usr/local/etc/rc.d/010.pgsql.sh stop
• /usr/local/etc/rc.d/postgresql stop

36) How To Check Whether Postgresql Server Is Up And Running?

Ans:

• /usr/local/etc/rc.d/010.pgsql.sh status
• /usr/local/etc/rc.d/postgresql status

37) What Are The Languages Which Postgresql Supports?

Ans:

Some of the languages which PostgreSQL supports are as follows:

• It supports a language of its own known as PL/pgSQL and it supports internal procedural languages.
• Pl/pgSQL can be compared to oracle, PL/SQL, etc.
• Languages such as Perl, Python, TCL can be used as embedded languages.

38) Explain About The Command Enable Debug?

Ans:

This command is used for enabling compilation of all libraries and applications. This process generally slows down the system and it also increases the binary file size. Debugging symbols are present which can assist developers in noticing bugs and problems associated with their script.

39) Explain About Functions In Postgresql?

Ans:

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.

40) Explain About Indices Of Postgresql?

Ans:

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.

41) Explain the PostgreSQL Observability?

Ans:

42) What Are The Different Data Types Supported By Postgresql?

Ans:

There are different data types which are supported they are:

• Arbitrary precision numeric’s
• Geometric primitives
• Arrays
• XML etc

43) Explain About Database Administration Tools?

Ans:

There are various data administration tools they are

• Psql
• Pgadmin
• Phppgadmin

44) Explain About Pgadmin?

Ans:

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.

45) How Do You Create A Data Base With Postgresql?

Ans:

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.

46) What Are The Various Enhancements To The Straight Relational Data Model By Postgresql?

Ans:

There are various enhancements provided to the straight relational data model by postgre SQl they are support for arrays which includes multiple values, inheritance, functions and extensibility. Jargon differs because of its object oriented nature where tables are called as classes.

47) Explain About Tokens?

Ans:

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.

48) Explain About String Constants?

Ans:

String constant contains a sequence of characters bound by single quotes. This feature is used during insertion of a character or passing character to database objects. PostgreSQL allows the usage of single quotes but embedded by a C style backslash. This feature is important in parsing data.

49) What Is Write Ahead Log?

Ans:

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.

50) How To Created A Database?

Ans:

/usr/local/bin/createdb mydatabase

51) How To List The Number Of Database?

Ans:

• su -l pgsqlpsql -l

52) How To Take Backup Of Database?

Ans:

/usr/local/bin/pg_dump mydatabase > mydatabase.pgdump

53) How To Create A Postgresql User?

Ans:

CREATE USER user WITH password ‘password’;

54) What Is A Sequence?

Ans:

A sequence is a special kind of database object designed for generating unique numeric identifiers. It is typically used to generate artificial primary keys.

Sequences are similar, but not identical, to the AUTO_INCREMENT concept in MySQL.

55) What Is A Ctid?

Ans:

CTIDs identify specific physical rows by their block and offset positions within a table. They are used by index entries to point to physical rows. A logical row’s CTID changes when it is updated, so the CTID cannot be used as a long-term row identifier. But it is sometimes useful to identify a row within a transaction when no competing update is expected.

56) Why Do I Get The Error “error: Memory Exhausted In Allocsetalloc()”?

Ans:

You probably have run out of virtual memory on your system, or your kernel has a low limit for certain resources. Try this before starting the server:

• ulimit -d 262144
• limit datasize 256m

57) Show PostgreSQL logical database architecture ?

Ans:

58) How Do I Create A Column That Will Default To The Current Time?

Ans:

Use CURRENT_TIMESTAMP:

CREATE TABLE test (x int, modtime TIMESTAMP DEFAULT CURRENT_TIMESTAMP );

59) How Do I Perform An Outer Join?

Ans:

PostgreSQL supports outer joins using the SQL standard syntax. Here are two examples:

SELECT * FROM t1 LEFT OUTER JOIN t2 ON (t1.col = t2.col); or SELECT * FROM t1 LEFT OUTER JOIN t2 USING (col);

60) How Do I Perform Queries Using Multiple Databases?

Ans:

There is no way to query a database other than the current one. Because PostgreSQL loads database-specific system catalogs, it is uncertain how a cross-database query should even behave.

contrib/dblink allows cross-database queries using function calls. Of course, a client can also make simultaneous connections to different databases and merge the results on the client side.

61) Is Possible To Create A Shared-storage Postgresql Server Cluster?

Ans:

PostgreSQL does not support clustering using shared storage on a SAN, SCSI backplane, iSCSI volume, or other shared media. Such “RAC-style” clustering isn’t supported. Only replication-based clustering is currently supported.

62) Does Postgresql Have Stored Procedures?

Ans:

PostgreSQL doesn’t.

63) How To Pronounce Postgresql?

Ans:

post-GRES-que-ell, per this audio file. Many people, however, just say “post-GRES”.

64) What Are New Features Postgre 9.1?

Ans:

As always, we can’t be certain what will go in and what won’t; the project has strict quality standards that not all patches can make before deadline. All we can tell you is what’s being worked on, which includes: synchronous replication, JSON support, security labels, nearest-neighbor geographic searches, SQL/MED external data connections, column-level collations, and index-only access. By the time 9.1 is released, though, this feature list will have changed considerably.

65) Does Postgresql Run On The Cloud?

Ans:

Yes. Like other open source databases, PostgreSQL is easy to run in virtual containers and is highly portable. Several companies have support for PostgreSQL in cloud hosting environments, including Heroku, GoGrid and Joyent.

66) How Does Postgresql Compare To “nosql”?

Ans:

The term “NoSQL” covers such a diverse array of non-relational database implementations … from tiny embedded databases like TokyoCabinet to massive clustered data processing platforms like Hadoop … that it’s impossible to comment on them as a general class. Non-relational databases preceded relational databases and have existed alongside them for forty years, so choosing between relational and nonrelational databases is nothing new. Users should choose the database whose features, implementation, and community support their current application needs. Further, using multiple different databases for large projects is fast becoming the norm, and PostgreSQL users are no exception.

67) How Does Postgresql Compare To Oracle/db2/ms Sql Server/informix?

Ans:

Our feature set is generally considered to be very competitive with other leading SQL RDBMSes. Certainly there are features some of them have which we don’t, and the reverse is also true. To date, only a few benchmarks have been published showing PostgreSQL to be within 10-30% of proprietary competitors. However, we have had many users migrate from other database systems � primarily Oracle and Informix � and they are completely satisfied with the performance of their PostgreSQL systems.

68) What is PostgreSQL vs MySQL?

Ans:

Postgres is an object-relational database, while MySQL is a purely relational database. This means that Postgres includes features like table inheritance and function overloading, which can be important to certain applications. Postgres also adheres more closely to SQL standards.

69. Explain About Concurrency With The Help Of Mvcc?

Ans:

Multi version concurrency control is used to manage concurrency. This feature is very useful because changes made in the database will not be visible to other users until the transaction is completed. This removes the need for read locks. ACID principles are given a further boost by this feature and can be implemented in general.

70)How can we change the columns datatype in PostgreSQL?

Ans:

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;

71) Provide an explanation About Write Ahead Logging?

Ans:

WAL or write-ahead logging is a standard method to ensure data integrity. It is a protocol or the correct rule to write both actions and changes into a transaction log. This feature is known to increase the reliability of the database by logging changes before any changes or updating to the database. This provides the log of the database in case of a database crash. This helps to start the work from the point it was discontinued.

72)Explain About Triggers?

Ans:

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.

73) How Do I Tell What Postgresql Version I Am Running?

Ans:

Run this query: SELECT version();

74) How Does Postgresql Compare To Mysql?

Ans:

This is a topic that can start several hours of discussion. As a quick summary, MySQL is the “easy-to-use, web developer” database, and PostgreSQL is the “feature-rich, standards-compliant” database. PostgreSQL is liberally licensed and owned by its community; MySQL is GPL-licensed and owned by Oracle. Beyond that, each database user should make his own evaluation; open source software makes doing comparisons very easy.

75) What is table inheritance in PostgreSQL?

Ans:

Inheritance in PostgreSQL allows you to create a child table based on another table, and the child table will include all of the columns in the parent table. Let’s take a database that’s used to store blueprints for different types of homes.

76) What is real datatype in PostgreSQL?

Ans:

real or float8 is a 4-byte floating-point number. numeric or numeric(p,s) is a real number with p digits with s number after the decimal point. The numeric(p,s) is the exact number.

77) What is decimal in PostgreSQL?

Ans:

Decimal,numeric –> It is a user specified precision, exact and range up to 131072 digits before the decimal point and up to 16383 digits after the decimal point.

78) What are the 4 types of number system?

Ans:

The four most common number system types are:

• Decimal number system (Base- 10)
• Binary number system (Base- 2)
• Octal number system (Base-8)
• Hexadecimal number system (Base- 16)

80) What is the difference between VARCHAR and TEXT in PostgreSQL?

Ans:

The only difference between TEXT and VARCHAR(n) is that you can limit the maximum length of a VARCHAR column, for example, VARCHAR(255) does not allow inserting a string more than 255 characters long.

81) What is maximum size of text data type in Postgres?

Ans:

In PostgreSQL, the text data type is used to keep the character of infinite length. And the text data type can hold a string with a maximum length of 65,535 bytes.

82) What is the difference between character varying and text in Postgres?

Ans:

As “Character Types” in the documentation points out, varchar(n) , char(n) , and text are all stored the same way. The only difference is extra cycles are needed to check the length, if one is given, and the extra space and time required if padding is needed for char(n) .

83) How do you increase the size of a column in PostgreSQL?

Ans:

Run the following command: alter table TABLE_NAME alter column COLUMN_NAME type character varying(120); This will extend the character varying column field size to 120.

84)How do I drop a constraint in PostgreSQL?

Ans:

The syntax for dropping a unique constraint in PostgreSQL is: ALTER TABLE table_name DROP CONSTRAINT constraint_name; table_name. The name of the table to modify.

85) Where is foreign key constraint in PostgreSQL?

Ans:

86) What is drop cascade in PostgreSQL?

Ans:

The CASCADE option allows you to remove the table and its dependent objects. The RESTRICT option rejects the removal if there is any object depends on the table. The RESTRICT option is the default if you don’t explicitly specify it in the DROP TABLE statement.

87) How do I show columns in PostgreSQL?

Ans:

• Using SQL query. Using query editor, run this query to show all columns with details: SELECT * FROM information_schema.columns WHERE table_schema = ‘schema_name’ AND table_name = ‘table_name’;
• Using psql. Using psql, you can use this command: \d+ table_name.
• Using TablePlus.

88) What is precision in PostgreSQL?

Ans:

In this syntax, the precision is the total number of digits and the scale is the number of digits in the fraction part. In PostgreSQL, the NUMERIC and DECIMAL types are equivalent and both of them are also a part of SQL standard.

89) What is length and precision in PostgreSQL?

Ans:

The scale of a numeric is the count of decimal digits in the fractional part, to the right of the decimal point. The precision of a numeric is the total count of significant digits in the whole number, that is, the number of digits to both sides of the decimal point.

90) What is UUID in PostgreSQL?

Ans:

UUID is an abbreviation for Universal Unique Identifier defined by RFC 4122 and has a size of 128-bit. It is created using internal algorithms that always generate a unique value. PostgreSQL has its own UUID data type and provides modules to generate them.

91) What is decimal in PostgreSQL?

Ans:

Decimal,numeric –> It is a user specified precision, exact and range up to 131072 digits before the decimal point and up to 16383 digits after the decimal point.