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 -

Topics

SQL Courses

Course

Introduction to SQL

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

Top 5 SQL Server Certifications: A Complete Guide

Unlock SQL Server certification success with our guide on paths, preparation with DataCamp, and the top certifications to enhance your career.
Matt Crabtree's photo

Matt Crabtree

8 min

PostgreSQL vs. MySQL: Choosing the Right Database for Your Project

Explore the key differences and similarities between PostgreSQL and MySQL to find the best database solution for your project's needs.
Jake Roach's photo

Jake Roach

8 min

SQL Developer Salaries: Expectations in 2024

In this article, we're going to learn what an SQL developer does, what factors impact SQL developer salaries, how the average SQL developer salary compares to the average salaries of other developer profiles, and how you can increase your salary as an SQL developer.
Elena Kosourova's photo

Elena Kosourova

7 min

Mastering SQL NOT EQUAL Operator: A Beginner's Guide

Unlock the power of SQL NOT EQUAL with our expert guide. Learn to refine data queries with practical examples and optimization tips for better analysis.
Abid Ali Awan's photo

Abid Ali Awan

5 min

SQL NOT IN Operator: A Comprehensive Guide for Beginners

Master SQL's NOT IN operator with this beginner's guide. Learn to filter data effectively, avoid common pitfalls, and explore efficient alternatives
Abid Ali Awan's photo

Abid Ali Awan

5 min

SQL CONTAINS: A Comprehensive Tutorial

Unlock the power of SQL CONTAINS for advanced text searches. Dive into logical operators, proximity searches, and wildcard uses for precise data analysis.
Abid Ali Awan's photo

Abid Ali Awan

5 min

See MoreSee More