17th
Apr
Postgresql Interview Questions

PostgreSQL Interview Questions

  • Nikita Singh
  • 17th Apr, 2021
  • 726 Followers

About PostgreSQL

PostgreSQL is a relational database management system that is highly extensible and has high compliance with technical standards. PostgreSQL handles a wide range of workloads from a machine to data warehouses or web services used by many users at a time. PostgreSQL automatically updates views and follows ACID ( Atomicity, Consistency, Isolation, Durability) properties. PostgreSQL has Materialized views, Triggers, Foreign keys, Stored procedures, etc. 

Postgresql Interview Questions

1) What is PostgreSQL?

PostgreSQL, also known as Postgres, is a free,powerful and open-source object-relational database management system emphasizing extensibility and technical standards compliance. It is designed to handle a range of workloads, from single machines to data warehouses or Web services with many concurrent users.It extends the SQL language combined with many features that safely store and scale the most complicated data workloads.

2) List some of the features of Postgresql?

Below are some features of PostgreSQL

  • User-defined types.
  • Table inheritance.
  • Sophisticated locking mechanism.
  • Foreign key referential integrity.
  • Views, rules, subquery.
  • Nested transactions (savepoints)
  • Multi-version concurrency control (MVCC)
  • Asynchronous replication

3) Enlist few advantages of Postgresql?

Major advantages of Postgresql are

  • Open Source DBMS
  • Diverse Community
  • ACID and Transaction support
  • Flexible Full-text search
  • HSTORE and JSONB
  • Easy documentation
  • Diverse kinds of replication
  • Support for professional replication
  • Support for stored procedures in various languages
  • Support custom aggregates and Professional triggers

4) What are the Indexes in PostgreSQL?

Indexes are special lookup tables that the database search engine can use to speed up data retrieval. Simply put, an index is a pointer to data in a table. An index in a database is very similar to an index in the back of a book.

PostgreSQL provides several index types: B-tree, hash, GiST, SP-GiST, and GIN. Each Index type uses a different algorithm that is best suited to different types of queries.

5) What are string constants in PostgreSQL?

A string constant in PostgreSQL is an arbitrary sequence of characters bounded by single quotes (') for example 'This is a string constant'.

PostgreSQL supports different types of String Constants like

  • String Constants with C-style Escapes
  • String Constants with Unicode Escapes
  • Dollar-quoted String Constants
  • Bit-string Constants

6) How to start or stop database server in PostgreSQL?

On macOS

To start the server

pg_ctl -D /usr/local/var/postgres start

To Stop the server

pg_ctl -D /usr/local/var/postgres stop

On windows

To start the server

pg_ctl -D "C:\Program Files\PostgreSQL\9.6\data" start

To Stop the server

pg_ctl -D "C:\Program Files\PostgreSQL\9.6\data" stop

On Linux

To start the server

sudo service postgresql start

To Stop the server

sudo service postgresql stop

7) How to change the columns datatype in Postgresql?

PostgreSQL change column type statement is used to change the data type of a column. It is used with an ALTER TABLE statement.

Example

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

8) How do you change constraints in PostgreSQL?

In PostgreSQL there is no way to change constraints. The easiest way to accomplish this is to drop the constraint and re-add it with the desired parameters. Of course any change of the constraint will be run against the current table data.

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

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

Example Query

SELECT ||/20 AS "Cube Root of 20";

10) How do we check whether Postgresql Server is running?

/sbin/service postgresql status or /etc/init.d/postgresql status command is used to check the running status of PostgreSQL.

11) What are Triggers in Postgres?

PostgreSQL Triggers are database callback functions, which are automatically performed/invoked when a specified database event occurs.

PostgreSQL trigger can be fired :

  • Before the operation is attempted on a row (before constraints are checked and the INSERT, UPDATE or DELETE is attempted)
  • After the operation has completed (after constraints are checked and the INSERT, UPDATE, or DELETE has completed)
  • Instead of the operation (in the case of inserts, updates or deletes on a view)

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

The Difference between the clustered index and non-clustered index in PostgreSQL .

Clustered Index Non clustered Index
ONLY 1 Clustered Index is occupied by a Table. A table may or may not have any Non-clustered Indexes.
It always has an Index Id of 0. In the Non-clustered Indexes, they have Index Id > 0.
A Primary Key constraint is used to build a Clustered Index. A Unique Key constraint is used to build a Non-clustered Index.
Clustered Index can enforce the Unique Key constraint. Non-clustered Index can enforce the Primary Key constraint.
It is faster to read in comparison with the non-clustered. It is quicker to add and update operations in comparison with the clustered index.
Clustered Index’s leaf nodes comprise of data pages of the table on which it is built. Non-clustered Index’s leaf nodes comprise of Index pages which covers Clustering Key or RID to locate Data Row.

13) Write command to create a Database in Postgres?

CREATE DATABASE command is used to create a new database in Postgresql. Syntax

CREATE DATABASE database_name

14) How to all Databases in Postgres?

A single Postgres server process can manage multiple databases at the same time. Each database is stored as a separate set of files in its own directory within the server’s data directory. To view all of the defined databases on the server you can use the \list meta-command or its shortcut \l.

Command

postgres=# \list

15) What is use of pgadmin?

pgAdmin is a management tool for PostgreSQL and derivative relational databases such as EnterpriseDB's EDB Advanced Server. It may be run either as a web or desktop application.

16) What are the indices of PostgreSQL?

PostgreSQL Indices are a common way to enhance database performance. An index allows the database server to find and retrieve specific rows much faster than it could do without an index. But indices also add overhead to the database system as a whole, so they should be used sensibly.

17) What is use of Array_To_String in PostgreSQL?

PostgreSQL ARRAY_TO_STRING() function is used to concatenate array elements using supplied delimiter and optional null string. It is similar to the implode function of PHP.

Example Query

SELECT array_to_string(ARRAY["Ram", "Kumar","is ", "PostgreSQL", "Learner"], ' ', '*');

Outputs: Ram Kumar is PostgreSQL Learner

18) Enlist data types available in PostgreSQL?

Different data types available in PostgreSQL are

  • Numeric Types: smallint, integer, bigint, decimal, real, serial etc
  • Monetary Types: money
  • Character Types: varchar, char, text
  • Binary Data Types: bytea
  • Date/Time Types: timestamp, date, time, interval
  • Boolean Type: boolean
  • Enumerated Types: enum
  • Geometric Types: point, line, lseg, box, path, polygon etc
  • Network Address Types: cidr, inet, macaddr
  • Bit String Types: BIT
  • Text Search Types: tsvector, tsquery
  • UUID Type: uuid
  • XML Type: xmlparse
  • JSON Types
  • Arrays
  • Composite Types
  • Range Types: int4range, numrange, tsrange, daterange
  • Object Identifier Types: oid, regproc, regrole etc
  • pg_lsn Type
  • Pseudo-Types: any, anyelement, anyenum etc

19) What is the dual table in PostgreSQL?

DUAL table is a special one-row, one-column table present by default in Oracle and other database installations. It is created as a view to easing porting problems, which allows code to remain compatible with Oracle SQL without obstructing the Postgres parser.

20) What is MVCC in PostgreSQL?

MVCC, which stands for multi version concurrency control, is one of the main techniques Postgres uses to implement transactions. Postgres handles transaction isolation by using MVCC to create a concept called "snapshots". Whenever a query starts, it takes a snapshot of the current state of the database.

21) What are tokens in PostgreSQL?

A token in PostgreSQL can be a keyword, an identifier, a quoted identifier, a literal (or constant), or a distinctive personality symbol.Tokens are generally separated with the aid of whitespace (space, tab, newline), however, need not be if there is no ambiguity (which is usually only the case if an exceptional persona is adjoining to some other token type).

22) What is table partitioning in PostgreSQL?

Table partitioning in PostgreSQL refers to 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. To divide data into partitions, we define a partition key and a partitioning method. The partition key is usually a column in the table, but it can also be an expression. The partitioning method is part of a partitioned table declaration and is determined according to the use case.

23) What is the maximum size of the table in PostgreSQL?

The maximum size of the table in PostgreSQL is 64TB. Some operating systems impose a file size limit that prevents files of this size from being created, so PostgreSQL stores table data in multiple files, each 1GB in size.

24) What is write-ahead logging in PostgreSQL?

Write-ahead logging or WAL, is an optimization Postgres uses to minimize disk I/O while still preventing data loss. Intuitively, whenever a transaction completes, a record of every single change that transaction made must have been written out to persistent storage. The changes that need to be written out include every single row that was inserted, deleted, or updated. If transactions completed before writing all of the changes made to disk and the power ever went out before they were, Postgres would suffer data loss.

25) List reserved words in PostgreSQL?

Here we are listing some reserved words of PostgreSQL:

ABORT, ABS, ASENSITIVE, ASC, DESC, BINARY, BIGINT, INT, BOTH, BREADTH, CACHE, CALL, CATALOG, COLLATE, DATABASE, DEFERRABLE etc .

For complete list you can visit: Reserved Keywords list in PostgreSQL

26) What is sequence in PostgreSQL?

PostgreSQL sequence is a special type of data created to generate unique numeric identifiers in the PostgreSQL database. It is most often used for the creation of artificial primary keys, sequences are similar but not identical to AUTO_INCREMENT in MySQL. The sequence objects (also known as sequence generators or simply sequences) are single-row tables created via a command from the command line: CREATE SEQUENCE.

27) How to enable debug mode in PostgreSQL?

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

Now, once you have done the proper set up of environment for debugging, go to edit /etc/postgresql/9.4/main/postgresql.conf and enable the debugger plugin.

28) How to find version of PostgreSQL?

Run below command on the terminal to find the version of Postgresql

postgres --version

29) Write syntax to create table in PostgreSQL?

CREATE TABLE statement is used to create a new table in Postgresql.

Syntax

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

30) What is difference between truncate and drop?

Difference Between Truncate and Delete

  • TRUNCATE removes all rows from a table. The operation cannot be rolled back and no triggers will be fired.
  • DELETE command is used to remove some or all rows from a table.

Delete Vs Truncate in Postgresql

31) Enlist different types of joins available in PostgreSQL?

There are 4 different types of joins supported by PostgreSQL :

  • PostgreSQL INNER JOIN (or sometimes called a simple join)
  • PostgreSQL LEFT OUTER JOIN (or sometimes called LEFT JOIN)
  • PostgreSQL RIGHT OUTER JOIN (or sometimes called RIGHT JOIN)
  • PostgreSQL FULL OUTER JOIN (or sometimes called FULL JOIN)

32) What is PostgreSQL Alias?

PostgreSQL Aliases are used to provide temporary names for columns or tables. You can create a temporary name for a column or a table by using PostgreSQL Alias.

33) What is a View in PostgreSQL?

PostgreSQL view is a logical table that represents data of one or more underlying tables through a SELECT statement.

Syntax for creating view in PostgreSQL

CREATE VIEW view_name AS query; 

34) How do you declare a user-defined exception in PostgreSQL?

PL/SQL allows you to define your own exceptions according to the need of your program. A user-defined exception must be declared and then raised explicitly, using either a RAISE statement or the procedure DBMS_STANDARD.RAISE_APPLICATION_ERROR.

The syntax for declaring an exception in PL/SQL

DECLARE 
   my-exception EXCEPTION; 

35) List some predefined exceptions in PostgreSQL?

There are mainly two types of exceptions in Postgresql they are System-defined exceptions and User-defined exceptions.

Below are the list of some predefined exceptions in PostgreSQL

  • ACCESS_INTO_NULL
  • CASE_NOT_FOUND
  • INVALID_CURSOR
  • NO_DATA_FOUND
  • ROWTYPE_MISMATCH
  • VALUE_ERROR
  • ZERO_DIVIDE
  • TOO_MANY_ROWS

36) What are maximum number of triggers you can apply on a table in PostgreSQL?

There can be n number of the trigger can have in a table that may be of 12 different types.

37) What are Cursors in PostgreSQL?

A SQL cursor in PostgreSQL is a read-only pointer to a fully executed SELECT statement's result set. Cursors are typically used within applications that maintain a persistent connection to the PostgreSQL backend.

38) How to execute a stored procedure in PostgreSQL?

To call a stored procedure in PostgreSQL, you use the CALL statement.

Syntax

CALL stored_procedure_name(parameter_list);

39) What is use of commit and rollback statement in PostgreSQL?

Commit and rollback are TCL commands

COMMIT: It is used to commit the current transaction. All changes made by the transaction become visible to others and are guaranteed to be durable if a crash occurs.

Syntax

COMMIT [ WORK | TRANSACTION ]

ROLLBACK: It is used to roll back the current transaction and causes all the updates made by the transaction to be discarded.

Syntax

ROLLBACK [ WORK | TRANSACTION ]

40) List types of cursors availables in PostgreSQL?

There are basically two types of Cursors available in PostgreSQL. They are

  • Implicit Cursor: Implicit cursors are automatically created by Oracle whenever an SQL statement is executed when there is no explicit cursor for the statement. Programmers cannot control the implicit cursors and the information in it. Whenever a DML statement (INSERT, UPDATE, and DELETE) is issued, an implicit cursor is associated with this statement.
  • Explicit Cursor: Explicit cursors are programmer-defined cursors for gaining more control over the context area. An explicit cursor should be defined in the declaration section of the PL/SQL Block. It is created on a SELECT Statement which returns more than one row.

41) What Is a Document in PostgreSQL?

A document in PostgreSQL is the unit of searching in a full-text search system; for example, a magazine article or email message. The text search engine must be able to parse documents and store associations of lexemes (keywords) with their parent document. Later, these associations are used to search for documents that contain query words.

42) How many indexes per table are allowed in PostgreSQL?

There is no PostgreSQL-imposed limit on the number of indexes you can create on a table. Of course, performance may degrade if you choose to create more and more indexes on a table with more and more columns. PostgreSQL has a limit of 1GB for the size of any one field in a table.

43) What is B tree index in PostgreSQL?

A b-tree index stands for "balanced tree" and is a type of index that can be created in relational databases. It's the most common type of index that is used in Oracle and PostgreSQL databases. It is also the default index type in Oracle.

44) What is a GiST index in PostgreSQL?

GiST is an extensible data structure, which allows users to develop indices over any kind of data, supporting any lookup over that data. GiST achieves this by adding an API to Postgres's index system anyone can implement for their specific data type.

45) What is pg_ctl command in PostgreSQL?

pg_ctl is a utility for initializing a PostgreSQL database cluster, starting, stopping, or restarting the PostgreSQL database server (Postgres), or displaying the status of a running server.

46) What is Initdb in PostgreSQL?

initdb is a PostgreSQL command that is used to create a new database cluster.

47) What is database cluster?

A database cluster is a collection of databases that are managed by a single server instance.

48) What is Nextval in Postgres?

NEXTVAL is a function to get the next value from a sequence. A sequence is an object which returns ever-increasing numbers, different for each call, regardless of transactions, etc. Each time you call NEXTVAL, you get a different number. This is mainly used to generate surrogate primary keys for your tables.

Leave A Comment :

Valid name is required.

Valid name is required.

Valid email id is required.