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;

65 comments

I am just love to have top phone interview questions becuase i have my job interviews and i really need that sheet.

Reply

I very much enjoyed this article.Nice article thanks for given this information. i hope it useful to many pepole.morephp jobs in hyderabad.

Reply

• Nice and good article. It is very useful for me to learn and understand easily. Thanks for sharing your valuable information and time. Please keep updatingAzure Online course Bangalore

Reply

Nice post. Thanks for sharing! I want people to know just how good this information is in your article. It’s interesting content and Great work.
Thanks & Regards,
VRIT Professionals,
No.1 Leading Web Designing Training Institute In Chennai.

And also those who are looking for
Web Designing Training Institute in Chennai
SEO Training Institute in Chennai
Photoshop Training Institute in Chennai
PHP & Mysql Training Institute in Chennai
Android Training Institute in Chennai

Reply

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

Reply

Download and install and office setup from
Office.com/setup

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

A fascinating discussion is definitely worth comment. I do believe that you ought to write more on this topic, it might not be a taboo matter but generally people don't talk about such subjects. To the next! Cheers!!
Click here to getMoreinformation.

Reply

Pretty article! I found some useful information in your blog, it was awesome to read, thanks for sharing this great content to my vision, keep sharing.

microsoft azure training

Reply

This is a wonderful article, Given so much info in it, Thanks for sharing. CodeGnan offers courses in new technologies and makes sure students understand the flow of work from each and every perspective in a Real-Time environmen python training in vijayawada. , data scince training in vijayawada . , java training in vijayawada. ,

Reply

Google sees high potential in reinforcement learning techniques using deep neural networks for qubit control optimization. Their abilities to harness non-local regularities of noisy control trajectories and to facilitate transfer learning between tasks have inspired researchers to adopt control methods built on deep reinforcement learning.Skyrocket in your Artificial Intelligence career with the twin engines of Python and R programmings. Join our Machine Learning using Python and R program and learn to script winning Machine Learning algorithms in Python and R. Use Python and R to enable regression analysis and to build predictive models. Orient yourself with Black Box techniques like Neural Networks and SVM.
machine learning course hyderabad

Reply

This is a wonderful article, Given so much info in it, Thanks for sharing. CodeGnan offers courses in new technologies and makes sure students understand the flow of work from each and every perspective in a Real-Time environmen python training in vijayawada. , data scince training in vijayawada . , java training in vijayawada. ,

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 Was An Amazing ! I Haven't Seen This Type of Blog Ever ! Thankyou For Sharing, data science course

Reply

Hello there! I simply wish to give you a huge thumbs up for your excellent information you have right here on this post. I'll be coming back to your web site for more soon.
Technology

Reply

wonderful article. Very interesting to read this article.I would like to thank you for the efforts you had made for writing this awesome article. This article resolved my all queries.
Data science Interview Questions
Data Science Course

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

keep up the good work. this is an Ossam post. This is to helpful, i have read here all post. i am impressed. thank you. this is our machine learning courses
machine learning courses | https://www.excelr.com/machine-learning-course-training-in-mumbai

Reply

Your article is very informative. It's a welcome change from other supposed informational content. Your points are unique and original in my opinion. I agree with many of your points.
Best Data Science training in Mumbai

Data Science training in Mumbai

Reply

wonderful article. Very interesting to read this article.I would like to thank you for the efforts you had made for writing this awesome article. This article resolved my all queries. keep it up.
data analytics course in Bangalore

Reply

Very interesting to read this article.I would like to thank you for the efforts you had made for writing this awesome article. This article inspired me to read more. keep it up.
Correlation vs Covariance
Simple linear regression

Reply

Cool stuff you have and you keep overhaul every one of us

Correlation vs Covariance

Reply

Very interesting blog. Many blogs I see these days do not really provide anything that attracts others, but believe me the way you interact is literally awesome.You can also check my articles as well.

Data Science In Banglore With Placements
Data Science Course In Bangalore
Data Science Training In Bangalore
Best Data Science Courses In Bangalore
Data Science Institute In Bangalore

Thank you..

Reply

Very interesting to read this article.I would like to thank you for the efforts you had made for writing this awesome article. This article inspired me to read more. keep it up.
Correlation vs Covariance
Simple linear regression

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

The development of artificial intelligence (AI) has propelled more programming architects, information scientists, and different experts to investigate the plausibility of a vocation in machine learning. Notwithstanding, a few newcomers will in general spotlight a lot on hypothesis and insufficient on commonsense application. machine learning projects for final year In case you will succeed, you have to begin building machine learning projects in the near future.

Projects assist you with improving your applied ML skills rapidly while allowing you to investigate an intriguing point. Furthermore, you can include projects into your portfolio, making it simpler to get a vocation, discover cool profession openings, and Final Year Project Centers in Chennai even arrange a more significant compensation.


Data analytics is the study of dissecting crude data so as to make decisions about that data. Data analytics advances and procedures are generally utilized in business ventures to empower associations to settle on progressively Python Training in Chennai educated business choices. In the present worldwide commercial center, it isn't sufficient to assemble data and do the math; you should realize how to apply that data to genuine situations such that will affect conduct. In the program you will initially gain proficiency with the specialized skills, including R and Python dialects most usually utilized in data analytics programming and usage; Python Training in Chennai at that point center around the commonsense application, in view of genuine business issues in a scope of industry segments, for example, wellbeing, promoting and account.

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

Well Explained !
Getting QBWC1005: QuickBooks Web Connector Failed to Run display? Don’t worry, make a call at 1-855-6OO-4O6O & get solutions instantly.

Reply

Nice & Informative Blog !
QuickBooks Payroll Error 15222 mainly occurs while upgrading QuickBooks Desktop or Payroll. If you find so, fix it by dialling our Qb experts at 1-855-6OO-4O6O.

Reply

Very interesting to read this article.I would like to thank you for the efforts you had made for writing this awesome article. This article inspired me to read more. keep it up.
Correlation vs Covariance
Simple linear regression
data science interview questions

Reply

Very interesting to read this article.I would like to thank you for the efforts you had made for writing this awesome article. This article inspired me to read more. keep it up.
Correlation vs Covariance
Simple linear regression
data science interview questions

Reply

Such a very useful article. Very interesting to read this article.I would like to thank you for the efforts you had made for writing this awesome article.

data science interview questions

Reply

This Was An Amazing ! I Haven't Seen This Type of Blog Ever ! Thankyou For Sharing, data sciecne course in hyderabad

Reply

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

Reply

Wonderful blog...! This information is very helpful for enhancing my knowledge and Thank you...! oracle training in chennai

Reply

Amazing Article ! I would like to thank you for the efforts you had made for writing this awesome article. This article inspired me to read more. keep it up.
Simple Linear Regression
Correlation vs covariance
data science interview questions
KNN Algorithm
Logistic Regression explained

Reply

Amazing Article ! I would like to thank you for the efforts you had made for writing this awesome article. This article inspired me to read more. keep it up. data science courses

Reply

I am looking for and I love to post a comment that "The content of your post is awesome" Great work!

Simple Linear Regression

Correlation vs Covariance

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

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.

Simple Linear Regression

Correlation vs covariance

KNN Algorithm

Logistic Regression explained

Reply

Amazing Article ! I would like to thank you for the efforts you had made for writing this awesome article. This article inspired me to read more. keep it up.
Simple Linear Regression
Correlation vs covariance
data science interview questions
KNN Algorithm
Logistic Regression explained

Reply

Very nice blogs!!! i have to learning for lot of information for this sites…Sharing for wonderful information.Thanks for sharing this valuable information to our vision. You have posted a trust worthy blog keep sharing, data sciecne course in hyderabad

Reply

Amazing Article ! I would like to thank you for the efforts you had made for writing this awesome article. This article inspired me to read more. keep it up.
Simple Linear Regression
Correlation vs covariance
data science interview questions
KNN Algorithm
Logistic Regression explained

Reply

I am looking for and I love to post a comment that "The content of your post is awesome" Great work!

Simple Linear Regression

Correlation vs Covariance

Reply

very well explained .I would like to thank you for the efforts you had made for writing this awesome article. This article inspired me to read more. keep it up.
Simple Linear Regression
Correlation vs covariance
data science interview questions
KNN Algorithm
Logistic Regression explained

Reply

very well explained. I would like to thank you for the efforts you had made for writing this awesome article. This article inspired me to read more. keep it up.
Logistic Regression explained
Correlation vs Covariance
Simple Linear Regression
data science interview questions
KNN Algorithm

Reply

Post a Comment