Skip to main content
HomeAbout SQLLearn SQL

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 -

SQL Courses

Introduction to SQL

BeginnerSkill Level
2 hr
372.4K
Learn how to create and query relational databases using SQL in just two hours.
See DetailsRight Arrow
Start Course
See MoreRight Arrow
Related
A duck codes in DuckDB

DuckDB makes SQL a first-class citizen on DataCamp Workspace

In this blog post, we list out all recent improvements that make it seamless and efficient to query data with SQL, all without leaving the tool.
Filip Schouwenaars's photo

Filip Schouwenaars

The 80 Top SQL Interview Questions and Answers for Beginners & Intermediate Practitioners

This article provides a comprehensive overview of 80 essential SQL questions and answers for job hunters, hiring managers, and recruiters, covering both general topics and technical questions.
Elena Kosourova's photo

Elena Kosourova

33 min

10 Portfolio-Ready SQL Projects for All Levels

Select your first—or next—SQL project to practice your current SQL skills, develop new ones, and create an outstanding professional portfolio.
Elena Kosourova's photo

Elena Kosourova

11 min

How to Use SQL in pandas Using pandasql Queries

Unleash the power of SQL within pandas and learn when and how to use SQL queries in pandas using the pandasql library for seamless integration.
Elena Kosourova's photo

Elena Kosourova

8 min

How to Practice SQL Using any Dataset with Workspace

Learn how DataCamp Workspace optimizes the experience of working with Jupyter notebooks and SQL. Discover how to effortlessly write SQL queries, connect to databases, analyze CSV files, and leverage the power of AI assistance
Richie Cotton's photo

Richie Cotton

9 min

QUALIFY: The SQL Filtering Statement You Never Knew You Needed

Learn about the SQL QUALIFY clause, an essential yet lesser-known filtering method in SQL. Understand its syntax, uses, and how it differs from other SQL filtering methods.
Kurtis Pykes 's photo

Kurtis Pykes

8 min

See MoreSee More