How To Create Database In Postgresql In Windows Using Pgadmin

Introduction

A database is simply a collection of information, or data, that is typically stored and accessed on some type of computer system and can be managed or organized in various ways. Databases can be very simple, used for just storing data, or more complex forms are used for modeling and design purposes. Databases will most often contain some types of tables that makes storing data more efficient. The PostgreSQL object-relational database system is designed to create a database and tables to effectively manage data. This tutorial will provided step-by-step instruction on how to create a database in Postgres.

Prerequisites

PostgreSQL must be properly installed, configured and running on the local machine in order to create a database in Postgres. Postgres for Windows can be installed by downloading the interactive installer from the Postgres website and then double clicking on the package installer.

Install PostgreSQL on MacOS

Postgres can be installed on MacOS with a few commands. It's recommended that the Homebrew repository be updated before installing Postgres. Update Homebrew by executing the following commands:

1	brew doctor && brew update

Now execute the following brew install command to install PostgreSQL:

Now execute the brew services start postgresql command to start the Postgres service on the Mac machine.

Install Postgres on Ubuntu

For a Linux machine, execute the following command in the terminal to install PostgreSQL on Ubuntu 18:

1	sudo apt update && sudo apt install postgresql-contrib postgresql

After the APT-GET repository has finished updating and installing PostgreSQL, start the service by executing the following command:

1	sudo service postgresql start

The following pg_ctl command may also be needed to start the service:

1	pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log start

Connect to psql

If the PgAdmin is not beng used, the database may have to be created with the SQL shell (psql) for Postgres. Execute the following command to access psql:

If the above command should fail or results in a " Fatal: role does not exist " error, try first accessing the postgres super user in the terminal with the following command:

Once the terminal has been accessed via postgres , the prompt should say: postgres@ . Access to psql should now be granted, as shown in the following screenshot:

Screenshot of installing PostgreSQL in order to create a database for a Postgres user role

Psql can also be accessed by connecting to a default database, like template1 , as shown in the following psql example:

1	psql -U postgres -d template1

NOTE: The -U and -d flags allow for specifying an username and database, respectively.

Set the Postgres User Password

It is a good idea to set a password for the postgres user, especially if using pgAdmin. Execute the following \password command while connected to psql to change the password for the postgres admin user:

Enter the new password when prompted.

PostgreSQL list databases command

Once inside of psql, execcute the following command to have Postgres return a table with all of the databases associated with the current user:

The results should resemble the following:

1
2
3
4
5
6
7
8

List of databases
Name | Owner | Encoding | Collate | Ctype| Access privileges
-------------+----------+----------+-------------+-------------+-----------------------
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
||| | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
||| | | postgres=CTc/postgres
( 2 rows)

Create a Database for Postgres

Now create a database with the SQL shell, or psql, for Postgres. A database can be created using either psql or the pgAdmin GUI. Just follow the guidelines and examples provided in the following sections.

Postgres 'CREATE DATABASE' SQL statement

The basic syntax for the CREATE DATABASE SQL statement is as follows:

If successfully executed, the above command should return a CREATE DATABASE response.

NOTE: Be certain that all SQL statements end with a semicolon ( ; ) to avoid syntax errors.

Use Postgres to create a database, if one does not already exist

Below is a simple workaround that allows for creating a database if one does not already exist. Execute the following \gexec query buffer to check each row that is returned:

1
2
3

SELECT 'CREATE DATABASE new_db'
WHERE NOT EXISTS
( SELECT FROM pg_database WHERE datname = 'new_db' )\gexec

Postgres: create a database with an owner

Following is another example that uses the unreserved OWNER SQL keyword to grant ownership privileges to a specific user when creating a database:

1	CREATE DATABASE new_db OWNER postgres;

The results of the above command should resemble the following image:

Screenshot using psql to create a database for PostgreSQL while granting ownership to a user

Connect to a PostgreSQL database

Execute the following \c or \connect command in the psql command-line interface to switch to another database with the current user:

The above command should return a response that resembles the following:

1	You are now connected to database "new_db" as user "orkb".

Postgres: grant all privileges on a database to user

Execute the following GRANT ALL PRIVILEGES ON DATABASE command to provide all privileges on a database to a specific user:

1	GRANT ALL PRIVILEGES ON DATABASE new_db TO orkb;

To switch to a different user, execute the following command:

Create a Postgres database using pgAdmin 4

ForMacOS and Windows systems, the pgAdmin graphical user interface, or GUI, can be downloaded and a database can be created with the click of a mouse. Once pgAdmin is running, navigate to its web interface in a browser tab; it will most likely be found at http:\\ .

Inside of the Browser object tree, located in the upper left-hand corner, right click and hover over the Create drop-down menu and then click Database… The following screenshot provides an example:

Screenshot of right clicking in pgAdmin to create a database for Postgres

Now a pop up modal for the CREATE DATABASE SQL statement will display as follows:

Screenshot of using pgAdmin to create a database for PostgreSQL

Enter a unique name for the database and add an optional comment, if desired. Now click the Save button.

The newly created database should now be visible under the drop-down Databases tree in the panel.

Conclusion

This tutorial provided step-by-step instructions, with examples, on how to create a database in Postgres. The tutorial explained how to install PostgreSQL on MacOS and on Ubuntu, how to connect to psql, set the Postgres user password and explained two ways of creating a database on Postgres by using the SQL Shell to open the command line and also by using the pgAdmin 4. The article also covered using the 'CREATE DATABASE' SQL statement, how to create a database with an owner and how to grant privileges on a database to a user. It is important to remember that all SQL statements must end with a semicolon ( ; ) to avoid syntax errors.