Skip to main content

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 -

Intermediate SQL Queries

Beginner
4 hours
1,485,254
Master the basics of querying tables in relational databases such as MySQL, SQL Server, and PostgreSQL.
See DetailsRight Arrow
Start Course

SQL for Joining Data

Beginner
5 hours
353,527
Join two or three tables together into one, combine tables using set theory, and work with subqueries in PostgreSQL.

Data Manipulation in SQL

Beginner
4 hours
156,082
Master the complex SQL queries necessary to answer a wide variety of data science questions and prepare robust data sets for analysis in PostgreSQL.
See all coursesRight Arrow
Related

What is SQL Used For? 7 Top SQL Uses

Discover the uses of SQL in industries and specific jobs. Plus, learn why the SQL language is so versatile and in demand.
Natassha Selvaraj's photo

Natassha Selvaraj

11 min

SQL Window Functions Cheat Sheet

With this SQL Window Functions cheat sheet, you'll have a handy reference guide to the various types of window functions in SQL.
DataCamp Team's photo

DataCamp Team

10 min

COALESCE SQL Function

COALESCE() is one of the handiest functions in SQL. Read this tutorial to learn how to master it.
Travis Tang 's photo

Travis Tang

FORMAT() SQL FUNCTION

FORMAT() is one of the most commonly used functions in SQL. Learn its main applications in this tutorial.
Travis Tang 's photo

Travis Tang

INSERT INTO SQL FUNCTION

INSERT INTO lets you add data to your tables. Learn how to use it in this tutorial.
Travis Tang 's photo

Travis Tang

COUNT() SQL FUNCTION

COUNT() lets you count the number of rows that match certain conditions. Learn how to use it in this tutorial.
Travis Tang 's photo

Travis Tang

See MoreSee More