Register Login
Python Photoshop SAP Java PHP Android C++ Hadoop Oracle Interview Questions Articles Other

PostgreSQL Interview Questions and Answers

Updated May 30, 2019

What is PostgreSQL?

PostgreSQL is an open source object-relational database management system. It runs on multiple operating systems and supports different programming languages like C, C++, Python, Ruby, Perl etc.

It supports JSON for non-relational queries and SQL for relational queries. It provides enhanced support for performance optimization and multiple data types.

What are the advantages and disadvantages of PostgreSQL?

The advantages of PostgreSQL are:

  • It runs on multiple operating systems like Windows, LINUX and Mac.
  • It supports different programming languages like C, C++, Python, Ruby, Perl etc.
  • It is not very complicated for beginners to learn.
  • It has the functionality of running many web applications and dynamic websites under the LAMP stack.
  • It can be used for location-based services as it supports geographic objects.

The advantages of PostgreSQL are:

  • Many open source apps do not support it.
  • It is slower than MySQL.
  • It does not support the entire ANSI SQL 92 standard.
  • Speed upgrades take a lot of time, unlike
  • Many hosting platforms do not support it.

What PostgreSQL command-line utility backs up PostgreSQL database settings?

In PostgreSQL, there is a pg_dump utility that is used to back up the database settings.

What are PostgreSQL databases?

A PostgreSQL database has an object-relational structure and has the following properties:

  • Foreign key referential integrity
  • Table inheritance
  • Data types defined by the user
  • Asynchronous replication
  • Enhanced locking systems
  • MVCC (Multi-Version Concurrency Control)
  • Custom functions developed in languages like Java and C.
  • Custom plugins can be added to improve the features and meet certain requirements.

What are the various features of PostgreSQL?

The different features of PostgreSQL are:

  • It runs on multiple operating systems like Windows, LINUX and Mac.
  • It supports different programming languages like C, C++, Python, Ruby, Perl etc.
  • It can be used for geographical information systems as it supports geographic objects.
  • It uses advanced locking mechanisms
  • It supports MVCC (Multi-Version Concurrency Control)
  • It has an enhanced server-side programming functionality
  • Supports client-server network architecture
  • Complete ACID Compliance

How to alter column datatype in PostgreSQL?  

The steps to change the data type of PostgreSQL are:

  • After the ALTER TABLE command, specify the name of the table that has the column.
  • Provide the name of the column that has the data type to be changed, after the ALTER COLUMN command.
  • Then give the new data type for the column after the SET TYPE or TYPE command.

The syntax is as follows:

ALTER TABLE table_name
ALTER COLUMN column_name [SET DATA] TYPE new_data_type;

How to create a table in PostgreSQL?

Tables in PostgreSQL can be made through the following steps:

  • After the CREATE TABLE command, provide the name of the table to be created.
  • Then provide the column name and the data type. Give the column constraints also like NOT NULL.
  • Then provide the table level constraints for setting the rules of the table.

The following syntax is used:

CREATE TABLE table_name (
column_name TYPE column_constraint,
table_constraint table_constraint
) INHERITS existing_table_name;

Here INHERITS is used to inherit the tables from an existing table.

How to delete a table in PostgreSQL?

Tables in PostgreSQL can be deleted through the following command:

DROP TABLE tablename

Here tablename is the name of the table that has to be deleted. The DROP command deletes the entire table.

How to add a column in PostgreSQL?

A table can be added through the following steps:

  • After the ALTER TABLE command specify the name of the table where a column has to be added.
  • After the ADD COLUMN command specify the name of the new column with its data type.

The following syntax has to be used:

ALTER TABLE table_name
ADD COLUMN new_column_name data_type;

How to reset serial in PostgreSQL?

Columns that have data type serial primary key are connected to a sequence. The next value in the sequence can be set using the following command

setval(<seqname>, <next_value>)

What is the maximum size of the table in PostgreSQL?

32 TB is the maximum size of the table in PostgreSQL

How to insert date in PostgreSQL?

In PostgreSQL, using the NOW() function gives you the current date and time. The return type of the function is the time zone and timestamp. It returns the time and date based on the database server’s settings. The syntax is:

SELECT NOW();

Another way to set date is to use the CURRENT_DATE function. The following syntax can be used:

SELECT CURRENT_DATE;

How to concatenate two strings in PostgreSQL?

Two strings can be concatenated in PostgreSQL through the CONCAT() function. The following syntax is used:

CONCAT(string1, string2);

Another way to add two strings is || operator. The syntax is as follows:

‘string1’||’string2’ AS result_string

What is the difference between PostgreSQL and MySQL?

The difference between PostgreSQL and MySQL are as follows:

PostgreSQL MySQL
Object-relational database management system. Relational database management system.
Highly extensible. Not as extensible as PostgreSQL.
It provides Data Domain Object. Does not provide Data Domain Object.
It is completely ACID compliant. It is ACID compliant only when NDB clusters and InnoDB are used.
It achieves high levels of concurrency by implementing MVCC. It supports MVCC only in InnoDB.
It has support for temporary tables and materialized views. It only supports temporary tables but not materialized views.

Recommended Posts:
×