Frequently Asked Basic PostgreSQL Interview Questions and Answers

Frequently Asked Basic PostgreSQL Interview Questions and Answers

PostgreSQL is the widely used open source database. If you are preparing for PostgreSQL interview, following list of basic PostgreSQL interview questions and answers might help you in your interview preparation. Following PostgreSQL interview questions and answers cover PostgreSQL basic concepts like feature and advantages of PostgreSQL, key difference between MySQL and PostgreSQL, basic PostgreSQL database administration commands and tools, general PostgreSQL database concepts like Stored Procedures, Functions, Triggers, Cursor, Index, Joins, Subqueries etc. 

1. What is PostgreSQL? What do you know about PostgreSQL?

PostgreSQL, often simply "Postgres", is an open-source object-relational database management system (ORDBMS) with an emphasis on extensibility and standards-compliance. It is released under the PostgreSQL License, a free/open source software license, similar to the MIT License. PostgreSQL is developed by the PostgreSQL Global Development Group, consisting of a handful of volunteers employed and supervised by companies such as Red Hat and EnterpriseDB.

Read more about PostgreSQL on Wikipedia and PostgreSQL official website

2. What are the various features and advantages of PostgreSQL?

This is very basic question and you should be updated on this. You should know why are you using PostgreSQL in your project, what features and advantages does PostgreSQL provide.

Visit official PostgreSQL website to learn more features and advantages of PostgreSQL

3. What are the key differences between MySQL and PostgreSQL? Which Open Source Database to Choose? Which one is best? 

MySQL and PostgreSQL are both free and open source powerful and full-featured databases. You should be able to compare these two databases. Here is the complete article on this. 

4. What are the various PostgreSQL database administration commands and tools?

You should know basic PostgreSQL database administration commands like creating users in PostgreSQL, setting up user credentials in PostgreSQL, change / update PostgreSQL user password, check whether PostgreSQL is up and running, commands to create, delete, drop, start, stop, restart, backup, restore PostgreSQL database, getting the list of all databases in PostgreSQL, finding out what version of PostgreSQL is running, PostgreSQL help and history commands, commands to get the list of all the tables in a PostgreSQL database, commands to turn on timing and checking how much time a query takes to execute, commands to see the list of available functions in PostgreSQL etc. Here is the complete article on this topic.

You should also know some of the PostgreSQL administration tools. You can visit Wiki andStackoverflow to get to know various PostgreSQL administration tools.

5. PostgreSQL database general concepts

Beside all this you should be well aware of datatypes in PostgreSQL, DDL, DML, DCL commands used in PostgreSQL. You should have good knowledge of Indexes, Joins, Subqueries, Stored Procedures, Functions, Triggers, Cursors etc.

I hope you will get benefited by these basic PostgreSQL interview questions and answers.

16 PostgreSQL Database Administration Commands

16 PostgreSQL Database Administration Commands

Following are basic PostgreSQL database administration commands which each PostgreSQL database administrator should know. These PostgreSQL database administration commands include creating users in PostgreSQL, setting up user credentials in PostgreSQL, change / update PostgreSQL user password, check whether PostgreSQL is up and running, commands to create, delete, drop, start, stop, restart, backup, restore PostgreSQL database, getting the list of all databases in PostgreSQL, finding out what version of PostgreSQL is running, PostgreSQL help and history commands, commands to get the list of all the tables in a PostgreSQL database, commands to turn on timing and checking how much time a query takes to execute, commands to see the list of available functions in PostgreSQL etc. Lets have a look on following PostgreSQL Database Administration Commands.

1. How to change PostgreSQL root user password?

$ /usr/local/pgsql/bin/psql postgres postgres
Password: (oldpassword)
# ALTER USER postgres WITH PASSWORD 'tmppassword';

$ /usr/local/pgsql/bin/psql postgres postgres
Password: (tmppassword)

Changing the password for a normal postgres user is similar as changing the password of the root user. Root user can change the password of any user, and the normal users can only change their passwords as Unix way of doing.

# ALTER USER username WITH PASSWORD 'tmppassword';

2. How to setup PostgreSQL SysV startup script?

$ su - root

# tar xvfz postgresql-8.3.7.tar.gz

# cd postgresql-8.3.7

# cp contrib/start-scripts/linux /etc/rc.d/init.d/postgresql

# chmod a+x /etc/rc.d/init.d/postgresql

3. How to check whether PostgreSQL server is up and running?

$ /etc/init.d/postgresql status
Password:
pg_ctl: server is running (PID: 6171)
/usr/local/pgsql/bin/postgres "-D" "/usr/local/pgsql/data"
[Note: The status above indicates the server is up and running]

$ /etc/init.d/postgresql status
Password:
pg_ctl: no server running
[Note: The status above indicates the server is down]

4. How to start, stop and restart PostgreSQL database?

# service postgresql stop
Stopping PostgreSQL: server stopped
ok

# service postgresql start
Starting PostgreSQL: ok

# service postgresql restart
Restarting PostgreSQL: server stopped
ok

5. How do I find out what version of PostgreSQL I am running?

$ /usr/local/pgsql/bin/psql test
Welcome to psql 8.3.7, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit

test=# select version();
version
----------------------------------------------------------------------------------------------------
PostgreSQL 8.3.7 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20071124 (Red Hat 4.1.2-42)
(1 row)

test=#

6. How to create a PostgreSQL user?

There are two methods in which you can create user.

Method 1: Creating the user in the PSQL prompt, with CREATE USER command.

# CREATE USER ramesh WITH password 'tmppassword';
CREATE ROLE

Method 2: Creating the user in the shell prompt, with createuser command.

$ /usr/local/pgsql/bin/createuser sathiya
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) n
Shall the new role be allowed to create more new roles? (y/n) n
CREATE ROLE

7. How to create a PostgreSQL Database?

There are two metods in which you can create two databases.

Method 1: Creating the database in the PSQL prompt, with createuser command.

# CREATE DATABASE mydb WITH OWNER ramesh;
CREATE DATABASE
Method 2: Creating the database in the shell prompt, with createdb command.

$ /usr/local/pgsql/bin/createdb mydb -O ramesh
CREATE DATABASE
* -O owner name is the option in the command line.

8. How do I get a list of databases in a Postgresql database?

# \l  [Note: This is backslash followed by lower-case L]
List of databases
Name | Owner | Encoding
----------+----------+----------
backup | postgres | UTF8
mydb | ramesh | UTF8
postgres | postgres | UTF8
template0 | postgres | UTF8
template1 | postgres | UTF8

9. How to Delete/Drop an existing PostgreSQL database?

# \l
List of databases
Name | Owner | Encoding
----------+----------+----------
backup | postgres | UTF8
mydb | ramesh | UTF8
postgres | postgres | UTF8
template0 | postgres | UTF8
template1 | postgres | UTF8

# DROP DATABASE mydb;
DROP DATABASE

10. Getting help on postgreSQL commands

\? will show PSQL command prompt help. \h CREATE will shows help about all the commands that starts with CREATE, when you want something specific such as help for creating index, then you need to give CREATE INDEX.

# \?

# \h CREATE

# \h CREATE INDEX

11. How do I get a list of all the tables in a Postgresql database?

# \d
On an empty database, you’ll get “No relations found.” message for the above command.

12. How to turn on timing, and checking how much time a query takes to execute?

# \timing — After this if you execute a query it will show how much time it took for doing it.

# \timing
Timing is on.

# SELECT * from pg_catalog.pg_attribute ;
Time: 9.583 ms

13. How To Backup and Restore PostgreSQL Database and Table?

We discussed earlier how to backup and restore postgres database and tables using pg_dump and psql utility.

14. How to see the list of available functions in PostgreSQL?

To get to know more about the functions, say \df+

# \df

# \df+

15. How to edit PostgreSQL queries in your favorite editor?

# \e
\e will open the editor, where you can edit the queries and save it. By doing so the query will get executed.

16. Where can I find the PostgreSQL history file?

Similar to the Linux ~/.bash_history file, postgreSQL stores all the sql command that was executed in a history filed called ~/.psql_history as shown below.

$ cat ~/.psql_history
alter user postgres with password 'tmppassword';
\h alter user
select version();
create user ramesh with password 'tmppassword';
\timing
select * from pg_catalog.pg_attribute;

47 comments

Very correct statistics furnished, Thanks a lot for sharing such beneficial data.
todaypk movies

Reply

Awesome blog. I enjoyed reading your articles. This is truly a great read for me. I have bookmarked it and I am looking forward to reading new articles. Keep up the good work!
courses in business analytics

data science interview questions

data science course in mumbai

data analytics courses

Reply

This website really has all the information and facts I wanted concerning this subject and didn’t know who to ask show.

Reply

This is a very good tip particularly to those fresh to the blogosphere. Short but very accurate technology information… Thank you for sharing this one. A must read post!

Reply

After reading your article I was amazed. I know that you explain it very well. And I hope that other readers will also experience how I feel after reading your article.
Data Analyst Course

Reply

Lockdown is running in the whole country due to coronavirus, in such an environment we are committed to provide the best solutions for QuickBooks Support Phone Number.
Contact QuickBooks Customer Service Phone Number to get in touch.
Dial QuickBooks Toll free Number : 1-844-908-0801

Reply

Attend online training from one of the best training institute Data Science Course in Hyderabad

Reply

I am impressed by the information that you have on this blog. It shows how well you understand this subject.
Data Science courses

Reply

Annabelle loves to write and has been doing so for many years.Backlink Indexer My GPL Store Teckum-All about Knowledge

Reply

I must admit that your post is really interesting. I have spent a lot of my spare time reading your content. Thank you very much!
data scientist course in hyderabad

Reply

Wonderful blog post. This is absolute magic from you! I have never seen a more wonderful post than this one. You've really made my day today with this. I hope you keep this up!
data scientist training and placement in hyderabad

Reply

Title:
Best Oracle Training Institute in Chennai | Infycle Technologies

Description:
Set your career goal towards Oracle for a wealthy future with Infycle. Infycle Technologies is the best Oracle training institute in Chennai, which gives the most trusted and best Oracle Training in hands-on practical training that will be guided by professional tutors in the field. In addition to this, the mock interviews will be given to the candidates, so that, they can face the interviews with full confidence. Apart from all, the candidates will be placed in the top MNC's with a great salary package. To get it all, call 7502633633 and make this happen for your happy life
Oracle Institute in Chennai with Placements

Reply

Thanks for posting this info. I just want to let you know that I just check out your site and I find it very interesting and informative. I can't wait to read lots of your posts.
DevOps Training in Hyderabad
DevOps Course in Hyderabad

Reply

Set your career goal towards Oracle for a wealthy future with Infycle. Infycle Technologies is one of the best Oracle DBA training institute in Chennai, that gives the most trusted and best Oracle DBA Training with various stages of Oracle in a 100% hands-on training which will be guided by professional tutors in the field. In addition to this, the mock interviews will be given to the candidates, so that, they can face the interviews with full confidence. Apart from all, the candidates will be placed in the top MNC's with a great salary package. To get it all, call 7502633633 and make this happen for your happy life.
BEST TRAINING IN CHENNAI

Reply

https://koladblog.com.ng

Reply

jan adhar card very usefull in rajsthan govt. All Process in Download See Now

Reply

I really thank you for the valuable info on this great subject and look forward to more great posts data science course in mysore

Reply

Really an awesome blog and informative content. Keep sharing more blogs with us. If you want to learn a data science course, follow the below link.
Best Data Science Training in Hyderabad

Reply

I’m happy I located this blog! From time to time, students want to recognize the keys of productive literary essays. Your first-class knowledge about this good post can become a proper basis for such people. nice one
business analytics course in hyderabad

Reply

Very Nice and op article Help Times
Very Nice and op article Ullu Web Series Cast

Reply

what an amazing and fabulous nice article Visit Kgf 2 Tamil Movie Download

Reply

Nice content, please checkout my website iPourit.in
Or
Visit www.Pourit.in

Reply
This comment has been removed by the author.

I like your all post. You have done really good work. Thank you for the information you provide, it helped me a lot. I hope to have many more entries or so from you.
Very interesting blog.평택출장안마
화성출장안마
의정부출장안마
동해출장안마
삼척출장안마

Reply
This comment has been removed by the author.

Запчасти для Грузовиков | Բեռնատարների և կցասայլերի պահեստամասեր | Repuestos para Camiones y Remolques | Резервни делови за камионе и приколице Kamyon ve Römork Yedek Parçaları | គ្រឿងបន្លាស់រថយន្ត | Reservdelar till lastbilar och slapvagnar | قطع غيار للشاحنات والمقطورات | Nahradni dily pro nakladni automobily a privesy | ट्रक के कलपुर्जे | 卡車配件 | Phụ tùng xe tải | חלקי משאית

Կայքի գրացուցակ | Вебсите дирецтори | Thư mục trang web | 網站目錄 | Web Directory | Каталог сайтов ТОП | Directory del sito web | ेबसाइट निर्देशिका | Adresář webových stránek | دليل الموقع | Free site directory | वेबसाइट निर्देशिका | ספריית אתרים

Seo продвижение opencart | Разработка Интернет-магазина под ключ | seo website | Создания магазина на OpenCart | SEO продвижения сайта | Разработка интернет магазина на движке OpenCart | SEO продвижения интернет магазина | Promosyon sa website | կայքի առաջխաղացում | ինչպես ստեղծել առցանց խանութ | קידום אתרים SEO

Bitcoin ծորակ | Bitcoin faucet | Depósito de criptomonedas con interés | ब्याज के साथ क्रिप्टोक्यूरेंसी जमा | Битцоин славина | Vòi bitcoin | Deposito in criptovaluta con interessi | Bitcoin ücretsiz musluk | bitcoin ukretsiz musluk

Ավտոակումբ | Club de Autos | نادي السيارات | Clube de Carros | Сlub de Voiture | Club Automobilistico | Araba Kulübü | Câu lạc bộ ô tô | ក្លឹបរថយន្ត | Ауто клуб | कार क्लब | 汽車俱樂部 | Автокөлікшілер клубы | Λέσχη Αυτοκινητιστών | Autoilijoiden klubi | מועדון רכב

Reply

If you love these chihuahua puppies as much as we do and you are looking forward to owning one, you are in the right place. We breed ,Tcup Chihuahua puppies. Click on button below to see more on출장마사지
출장마사지
출장마사지
출장마사지
출장마사지
출장마사지
출장마사지
출장마사지

Reply

Art benefit form state most fly. Understand sea within benefit today own.entertainment

Reply

Post a Comment