Managing Databases in PostgreSQL

If you are an aspiring data scientist or are curious about data in general, you must know the importance of relational databases. SQL is a language which lets you query relational databases and ask meaningful questions. For a beginner who is getting started in handling databases, it is useful to know how to quickly get up and running with a database. This comprises of three basic operations -

• Creating a Database
• Dropping a Database
• Selecting a Database

In this tutorial, you will be performing these operations using PostgreSQL. This tutorial assumes that you already have some background on SQL in general and you have PostgreSQL installed on your system. If you do not have it installed, please check this article which guides you about downloading and installing PostgreSQL.

If you are all set up with your PostgreSQL installation, let's get started.

Creating a Database

Let's open up pgAdmin (which comes with the PostgreSQL installation). pgAdmin provides you with a friendly graphical interface, and it lets you perform all the basic SQL stuff. The pgAdmin interface looks like -

From the above image, you can see that the corresponding PostgreSQL system has two databases already -

• DataCamp_Courses
• postgres

You will create a new one now. For that, simply right click on the Databases tab and select New Database... from the options. For your reference - https://bit.ly/2UEfk62.

If you were able to create a database successfully, then it should appear on pgAdmin's home screen -

When you are creating the database, you will be provided with a number of options. Do not let that intimidate you. Just give the name of the database that you want to create and hit enter.

One question that you might have is - what is the need for creating different databases?

Well, think of a scenario where you use a central PostgreSQL system for managing all the database related things which includes managing the data from different types of applications. You might not want to mix everything and collate in one single database, right? It's better to separate the databases w.r.t the applications.

Dropping a Database

You might want to drop a database which you do not use anymore. Simply right-click on the database you want to delete and select Delete/Drop from the options. For your reference - https://bit.ly/2S7iL8x.

Selecting a Database

Let's do this from command prompt/terminal. With the standard PostgreSQL installation comes psql (SQL Shell). It looks just like a command prompt -

Open up psql prompt and keep hitting enter (if you have anything to specifically connect too, feel free give the credentials accordingly) until you are prompted to enter the password. Enter the password you provided during installation and hit enter. The PostgreSQL prompt is postgres=#.

Let's first list all the databases PostgreSQL currently has by giving \l command. It outputs something like this -

Let's select a database to execute a simple SQL query. For connecting to a database, you give the following command -

\c School

School is the name of the database to be connected. Once you are connected to the database, the prompt also gets changed to the name of the database -

Let's create a simple table named Students in this database. The table will have two columns -

• Roll_no
• Name

You are free to specify the data-types and constraints according to your favorites. For creating the table, you will need a query similar to this -

Let's go ahead and insert an element into the table and view the table after that -

That is all for today's tutorial. If you want to learn more about SQL, check out the following DataCamp courses -

• Intro to SQL for Data Science
• Joining Data in SQL