Postgresql Interview Questions And Answers

postgresql interview questions and answers   Top 41 Postgresql Interview Questions And Answers

1) What is PostgreSQL?

It is open source object-relational database management system(RDBMS) which is very powerful in nature.


2) What are the key features of PostgreSQL?

Following are the key features of PostgreSQL:

● It runs on all the major operating systems
● It can handle complex queries
● It can work in sync with all the major development languages.


3) How alter function can be used in PostgreSQL?

Basic syntax:

ALTER FUNCTION name ( [ type [, ...] ] ) RENAME TO new_name


4) How to collect statistics of database in PostgreSQL?

Basic syntax:

ANALYZE [ VERBOSE ] [ table [ (column [, ...] ) ] ]


5) How to create schema in PostgreSQL?

Basic syntax:

CREATE SCHEMA schema_name
[ AUTHORIZATION username ] [ schema_element [ ... ] ]
CREATE SCHEMA AUTHORIZATION username
[ schema_element [ ... ] ]


6) How to create tablespace in PostgreSQL?

Basic syntax:

CREATE TABLESPACE tablespace_name [ OWNER username ] LOCATION 'directory'


7) How to create view in PostgreSQL?

Basic syntax:

CREATE [ OR REPLACE ] VIEW name [ ( column_name [, ...] ) ] AS query


8) How to define cursor in PostgreSQL?

Basic syntax:

DECLARE name [ BINARY ] [ INSENSITIVE ] [ [ NO ] SCROLL ]
CURSOR [ { WITH | WITHOUT } HOLD ] FOR query
[ FOR { READ ONLY | UPDATE [ OF column [, ...] ] } ]


9) How to commit the current transaction in PostgreSQL?

Basic syntax:

COMMIT [ WORK | TRANSACTION ]


10) How to create cluster in PostgreSQL?

Basic syntax:

CLUSTER index_name ON table_name
CLUSTER table_name
CLUSTER


11) How to deallocate a prepared statement in PostgreSQL?

Basic syntax:

DEALLOCATE [ PREPARE ] plan_name


12) How to drop conversation in PostgreSQL?

Basic syntax:

DROP CONVERSION name [ CASCADE | RESTRICT ]


13) How to drop database in PostgreSQL?

Basic syntax:

DROP DATABASE name


14) How to create trigger in PostgreSQL?

Basic syntax:

CREATE TRIGGER name { BEFORE | AFTER } { event [ OR ... ] }
ON table [ FOR [ EACH ] { ROW | STATEMENT } ]
EXECUTE PROCEDURE func_name ( arguments )


15) How to give grant in PostgreSQL?

Basic syntax:

GRANT { { SELECT | INSERT | UPDATE | DELETE | RULE | REFERENCES | TRIGGER }
[,...] | ALL [ PRIVILEGES ] }
ON [ TABLE ] table_name [, ...]
TO { username | GROUP group_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

GRANT { { CREATE | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] }
ON DATABASE db_name [, ...]
TO { username | GROUP group_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]


16) How to listen for new notification in PostgreSQL?

Basic syntax:

LISTEN name


17) How to lock a table in PostgreSQL?

Basic syntax:

LOCK [ TABLE ] name [, ...] [ IN lock_mode MODE ] [ NOWAIT ]

Here lock_mode can be any one of the below modes:

ACCESS SHARE | ROW SHARE | ROW EXCLUSIVE | SHARE UPDATE EXCLUSIVE | SHARE | SHARE ROW EXCLUSIVE | EXCLUSIVE | ACCESS EXCLUSIVE


18) How to generate notification in PostgreSQL?

Basic syntax:

NOTIFY name


19) How to rebuild indexes in PostgreSQL?

Basic syntax:

REINDEX { DATABASE | TABLE | INDEX } name [ FORCE ]


20) How to revoke all the access in PostgreSQL?

Basic syntax:

REVOKE [ GRANT OPTION FOR ]
{ { SELECT | INSERT | UPDATE | DELETE | RULE | REFERENCES | TRIGGER }
[,...] | ALL [ PRIVILEGES ] }
ON [ TABLE ] table_name [, ...]
FROM { username | GROUP group_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]

REVOKE [ GRANT OPTION FOR ]
{ { CREATE | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] }
ON DATABASE db_name [, ...]
FROM { username | GROUP group_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]


21) How to restore the value of runtime parameter to default in PostgreSQL?

Basic syntax:

RESET name
RESET ALL


22) How to change the runtime parameter in PostgreSQL?

Basic syntax:

SET [ SESSION | LOCAL ] name { TO | = } { value | 'value' | DEFAULT }
SET [ SESSION | LOCAL ] TIME ZONE { time_zone | LOCAL | DEFAULT }


23) How to set the session authorization in PostgreSQL?

Basic syntax:

SET [ SESSION | LOCAL ] SESSION AUTHORIZATION username
SET [ SESSION | LOCAL ] SESSION AUTHORIZATION DEFAULT
RESET SESSION AUTHORIZATION


24) How to set the constraints in PostgreSQL?

Basic syntax:

SET CONSTRAINTS { ALL | name [, ...] } { DEFERRED | IMMEDIATE }


25) How to set the characteristics of the transaction in PostgreSQL?

Basic syntax:

SET TRANSACTION transaction_mode [, ...]
SET SESSION CHARACTERISTICS AS TRANSACTION transaction_mode [, ...]


26) How to show the value of runtime parameter in PostgreSQL?

Basic syntax:

SHOW name
SHOW ALL


27) How to empty a table in PostgreSQL?

Basic syntax:

TRUNCATE [ TABLE ] name


28) How to stop listening to the notification in PostgreSQL?

Basic syntax:

UNLISTEN { name | * }


29) What is vacuum in PostgreSQL?

Vacuum is use for garbage collection and for database analysis.

Basic syntax:

VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ table ]
VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] ANALYZE [ table [ (column [, ...] ) ] ]


30) How to define savepoint in PostgreSQL?

Basic syntax:

SAVEPOINT savepoint_name


31) How to rollback to a defined savepoint in PostgreSQL?

Basic syntax:

ROLLBACK [ WORK | TRANSACTION ] TO [ SAVEPOINT ] savepoint_name


32) How to move a cursor in PostgreSQL?

Basic syntax:

MOVE [ direction { FROM | IN } ] cursor_name


33) How to prepare a statement for execution in PostgreSQL?

Basic syntax:

PREPARE plan_name [ (data_type [, ...] ) ] AS statement


34) How to load a shared library file in PostgreSQL?

Basic syntax:

LOAD 'filename'


35) How to get an execution plan of a statement in PostgreSQL?

Basic syntax:

EXPLAIN [ ANALYZE ] [ VERBOSE ] statement


36) How to drop a view in PostgreSQL?

Basic syntax:

DROP VIEW name [, ...] [ CASCADE | RESTRICT ]


37) How to drop a trigger in PostgreSQL?

Basic syntax:

DROP TRIGGER name ON table [ CASCADE | RESTRICT ]


38) How to drop type in PostgreSQL?

Basic syntax:

DROP TYPE name [, ...] [ CASCADE | RESTRICT ]


39) How to remove a user account from database in PostgreSQL?

Basic syntax:

DROP USER name


40) How to drop sequence in PostgreSQL?

Basic syntax:

DROP SEQUENCE name [, ...] [ CASCADE | RESTRICT ]


41) How to drop schema in PostgreSQL?

Basic syntax:

DROP SCHEMA name [, ...] [ CASCADE | RESTRICT ]