Skip to main content
HomeTutorialsSQL

Managing Databases in PostgreSQL

In this tutorial, you will learn how to create, drop and select a database in SQL.
Feb 2019  · 4 min read

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 -

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 - Creating a Database

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 -

Creating a Database

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.

Creating a Database

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 -

Selecting a Database

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 -

Selecting a Database

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 -

Selecting a 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 -

Selecting a Database

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

Selecting a Database

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

Topics

SQL Courses

Course

Introduction to SQL

2 hr
661.7K
Learn how to create and query relational databases using SQL in just two hours.
See DetailsRight Arrow
Start Course
See MoreRight Arrow
Related

blog

SQL Server, PostgreSQL, MySQL... what's the difference? Where do I start?

In this tutorial, you'll learn some of the basic differences between SQL dialects and where you should begin.
Mona Khalil's photo

Mona Khalil

5 min

tutorial

Beginner's Guide to PostgreSQL

In this tutorial, you will learn how to write simple SQL queries in PostgreSQL.
Sayak Paul's photo

Sayak Paul

13 min

tutorial

SQL Database Overview Tutorial

In this tutorial, you'll learn about databases in SQL.
DataCamp Team's photo

DataCamp Team

3 min

tutorial

How to Install PostgreSQL on Windows and Mac OS X

In this tutorial, you will learn how to install PostgreSQL on two different operating systems - Windows and Mac.
Sayak Paul's photo

Sayak Paul

6 min

tutorial

Using PostgreSQL in Python

Discover how to create, connect to and manage PostgreSQL databases using Python’s psycopg2 package.
Javier Canales Luna's photo

Javier Canales Luna

14 min

tutorial

10 Command-line Utilities in PostgreSQL

In this tutorial, learn about 10 handy command-line utilities in PostgreSQL which can enable you to interact with databases efficiently.
Sayak Paul's photo

Sayak Paul

7 min

See MoreSee More