PostgreSQL

Connect to PostgreSQL Database Command Line Windows

In our previous articles, we talked about the PostgreSQL server and the databases created with this server’s help. However, we stated earlier that installing this RDBMS on a Windows 10 system is a little more complex than the Linux operating system. Therefore, most of the users also find using the PostgreSQL databases on Windows 10 quite challenging. To ease their difficulty, we wanted to teach them the first step of using the PostgreSQL databases, i.e., connecting to the PostgreSQL databases. By now, you would have realized the goal of this discussion. It aims to guide you on the connection mechanism of a PostgreSQL database while using the Windows 10 command prompt.

Note: You can also play around with the PostgreSQL databases while using the SQL Shell or psql; however, the goal of this article was different from this. 

How to connect to the PostgreSQL Database through Windows 10 Command Line Interface (CLI)?

For connecting to any desired PostgreSQL database through Windows 10 CLI, you will have to perform the following five steps, whereas the sixth one is optional. However, we would like to state over here that you should first read all of these steps thoroughly and then start performing them so that you can accomplish the desired task correctly without making any mistakes.

Step # 1: Set up the Environment Variable of your Windows 10 System:

Before starting to use the Windows 10 command prompt for accessing the PostgreSQL environment, you need to set up your system’s environment variable. Otherwise, you will face issues while accessing the PostgreSQL environment on your system. For setting up the required environment variable, you have to type “env” in your Windows 10 search bar and click on the “Edit the system environment variables” result.

After choosing this option, the System Properties window will pop up on your screen. You need to click on the third tab named the “Advanced” tab within this window. In that tab, you need to locate the Environment Variables button and click on it.


Then, go to the System variables section and double click on the Path option located over there. This will let you add the Path of your PostgreSQL server’s bin directory to the PATH environment variable so that the PostgreSQL environment can be accessed easily from the Windows 10 command prompt.

From the PATH environment variable, you need to click on the “New” button so that you can add a new path to it.

Here, you need to copy and paste the path of your PostgreSQL server’s bin directory at the specified location. After adding this path, you can proceed further by clicking on the “OK” button. After that, you can also close the System Properties window.

Step # 2: Access the Command Prompt through your Windows 10 System:

Once you have correctly set up the required environment variable for your PostgreSQL server, the next step is to launch the command prompt on Windows 10. For that, you have to use the Windows 10 search bar again and look for “cmd” in it. You will instantly be able to see the Command Prompt result on which you need to click for launching it.

The default Command Prompt window of our Windows 10 system is shown below. Initially, no commands will be executed, but you will see how you can run commands within Windows 10 command prompt in the following step.

Step # 3: Enter the PostgreSQL Environment through the Command Prompt:

Now, you need to access the PostgreSQL environment through Windows 10 command prompt. For that, you need to run the command shown below:

> psql –U postgres

Here, “postgres” represents the default username for the PostgreSQL server. During the installation of the PostgreSQL server, and even after that, you can create a new user. However, since we did not create any new user for our PostgreSQL server, we will have to access the PostgreSQL environment through the user-created by default during the installation. If you have also not created a new user, then your default username will also be “postgres”.

When you run the above-mentioned command, you will be asked to provide your specified user account password. For the default user account, you are requested to create a password for accessing it during the PostgreSQL server’s installation; therefore, in this case, we will be providing that password that we had set up for the default user account during the installation of the PostgreSQL server on our Windows 10 system.

Soon after providing the correct password for the specified user account, you will immediately enter the PostgreSQL environment while staying within Windows 10 command prompt.

Step # 4: Create a Sample PostgreSQL Database:

Before we try to connect to a PostgreSQL database, we will first create a sample database using the following command. If you already have the desired PostgreSQL database created on your Windows 10 system, you can skip this step.

# CREATE DATABASE sampleDB WITH ENCODING 'UTF8' LC_COLLATE=‘English_United States' LC_CTYPE=‘English_United States';

Here, sampleDB represents the name of the sample PostgreSQL database that we are attempting to create with this command.

If you have provided all the parameters of the above-mentioned command correctly, then this command will successfully execute hence producing the response of “CREATE DATABASE” on your command prompt as shown in the image below:

Since this was just a sample PostgreSQL database and we did not intend to do anything else with it as such rather we just wanted to show you how you can connect to this database through Windows 10 command prompt; therefore, we have not created any tables within this database, i.e., our sample PostgreSQL database is empty.

Step # 5: Connect to your Newly Created PostgreSQL Sample Database:

For connecting to the newly created PostgreSQL sample database, the command that needs to be executed is very simple and is as follows:

# psql sampleDB

Here, sampleDB corresponds to the database to which we want to connect using the Windows 10 command prompt. You can replace it with the name of your desired PostgreSQL database to which you want to connect.

If your requested database exists and there are also not any other errors, then you will be able to connect to the specified database successfully, but you will not be presented with an acknowledgment on the command prompt as shown below:

Step # 6: Execute the PostgreSQL Queries on your Sample PostgreSQL Database (Optional):

Once you have connected successfully to your desired PostgreSQL database while using the Windows 10 command prompt, you can execute all the queries of your choice for manipulating your data. However, if you want to quit from the PostgreSQL environment after connecting to your desired database, all you need to do is to execute the following query, and you will be out of the PostgreSQL environment.

# \q

Conclusion:

This article gave the users a good head start on connecting to the PostgreSQL databases while using the Windows 10 command prompt. We are hoping that after going through this guide, you will not face any issues, at least while connecting to your PostgreSQL databases in Windows 10, especially if you intend to use the command prompt for this purpose.

About the author

Aqsa Yasin

I am a self-motivated information technology professional with a passion for writing. I am a technical writer and love to write for all Linux flavors and Windows.