PostgreSQL Interview Questions :-
1. Define what is PostgreSQL?
This is regarded as one of the most successful open source databases in the world. This is also used to create advanced applications. This relies on the Object-relational database management system. Familiarity with UNIX and Linux can be an added advantage while using PostgreSQL.
2. State some of the advanced features of PostgreSQL?
These are the following features which are present in PostgreSQL they are
- Object-relational database
- Extensibility and support for SQL
- Database validation and flexible API
- Procedural languages and MVCC
- Client-server and WAL.
3. Explain about Write Ahead logging?
This feature increases the reliability of the database by logging changes before any changes or updations to the database. This provides a log of a database in cases of a database crash. This helps to start the work from the point it was discontinued.
4. Explain about Multi-version concurrency control?
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.
5. How to start the database server?
/usr/local/etc/rc.d/010.pgsql.sh start
/usr/local/etc/rc.d/postgresql start
6. How to stop the database server?
/usr/local/etc/rc.d/010.pgsql.sh stop
/usr/local/etc/rc.d/postgresql stop
7. How to check whether PostgreSQL server is up and running?
/usr/local/etc/rc.d/010.pgsql.sh status
/usr/local/etc/rc.d/postgresql status?
8. Define what are the languages which PostgreSQL supports?
Some of the languages which PostgreSQL supports are as follows: –
It supports a language of it is own known as PL/pgSQL and it supports internal procedural languages. Pl/pgSQL can be compared to the Oracle, PL/SQL, etc. Languages such as Perl, Python, TCL can be used as embedded languages.
9. Explain about the command enable-debug?
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.
10. Explain about functions in PostgreSQL?
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.
11. Explain about indices of PostgreSQL?
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 backward. Expression index could be created with the result of an expression. Partial index created with the addition of WHERE clause.
12. Explain about Triggers?
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.
13. Define what are the different data types supported by PostgreSQL?
There are different data types which are supported they are: –
- Arbitrary precision numeric’s
- Geometric primitives
- Arrays
- XML etc
Users can create their own indexes and make them indexed.
14. Explain about database administration tools?
There are various data administration tools they are
- Psql
- Pgadmin
- Phppgadmin
Most of these tools are front end administration tools and web-based interfaces. Out of this phppgadmin is the most popular one.
15. Explain about pgadmin?
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.
PostgreSQL Questions Pdf Free Download::
16. How do you create a database with PostgreSQL?
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.
17. Define what are the various enhancements to the straight relational data model by PostgreSQL?
There are various enhancements provided to the straight relational data model by posture SQL they are supported for arrays which include multiple values, inheritance, functions, and extensibility. Jargon differs because of its object-oriented nature where tables are called classes.
18. Explain about tokens?
Tokens are also known to contain several special character symbols. It can be considered as keyword, constant, identifier and quoted identifier. Keywords include predefined SQL meanings and SQL commands. Variable names such as tables, columns, etc are represented by identifiers.
19. Explain about string constants?
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.
21. Explain about concurrency with the help of MVCC?
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 reading locks. ACID principles are given a further boost by this feature and can be implemented in general.
22. How to create a database?
/usr/local/bin/create my database
23. How to list the number of databases?
so -l pgsql
psql -l
24. How to take the backup of the database?
/usr/local/bin/pg_dump my database > my database.pgdump
25. How to create a PostgreSQL user?
CREATE USER user WITH password ‘password’