Summary: in this tutorial, you will learn how to connect to the PostgreSQL Database Server via an interactive terminal program called psql and via the pgAdmin application.
When you installed the PostgreSQL database server, the PostgreSQL installer also installed some useful tools for working with the PostgreSQL database server. In this tutorial, you will learn how to connect to the PostgreSQL database server via the following tools:
- psql – a terminal-based front-end to PostgreSQL database server.
- pgAdmin – a web-based front-end to PostgreSQL database server.
1) Connect to PostgreSQL database server using psql
psql is an interactive terminal program provided by PostgreSQL. It allows you to interact with the PostgreSQL database server such as executing SQL statements and managing database objects.
The following steps show you how to connect to the PostgreSQL database server via the psql program:
First, open the Command Prompt on Windows or Terminal on Unix-like systems.
Second, use the psql command to connect to the PostgreSQL server:
psql -U postgresCode language: Shell Session (shell)In this command:
psql: Invoke the psql program.-U postgres: Specify the user that connects to the PostgreSQL server. The -U option means user. Note that you need to use -U in uppercase, not lowercase.
The command will prompt you to enter the password for the postgres user:
Password for user postgres:Code language: Shell Session (shell)You need to provide the password for the postgres user and press Enter (or Return key). Note that the password is the one that you entered when you installed the PostgreSQL server.
After entering the password correctly, you’ll connected to the PostgreSQL server. The command prompt will change to:
postgres=#Code language: Shell Session (shell)Third, execute the following command to retrieve the PostgreSQL version:
SELECT version();Code language: SQL (Structured Query Language) (sql)Please ensure to conclude the statement with a semicolon (;). Upon pressing Enter, psql will return the current PostgreSQL version on your system.
The output will look like:
version
------------------------------------------------------------
PostgreSQL 16.1, compiled by Visual C++ build 1937, 64-bit
(1 row)Code language: plaintext (plaintext)2) Connect to PostgreSQL database server using pgAdmin
The second way to connect to a database is by using a pgAdmin application.
The pgAdmin application allows you to interact with the PostgreSQL database server via an intuitive user interface.
The following illustrates how to connect to a database using the pgAdmin application:
First, launch the pgAdmin application from the Start menu
The pgAdmin application will launch on the web browser as shown in the following picture:
Second, right-click the Servers node and select Register > Server… menu to create a server
Third, enter the server name such as Local, and click the Connection tab:
Fourth, enter the host and password for the postgres user and click the Save button:
Fifth, click on the Servers node to expand the server. By default, PostgreSQL has a database named postgres:
Sixth, open the query tool by selecting the menu item Tool > Query Tool:
Seventh, enter the query in the Query Editor and click the Execute button, you will see the result of the query displayed in the Data Output tab:
Connect to PostgreSQL Server from other applications
Any application that supports ODBC or JDBC can connect to the PostgreSQL server.
Furthermore, if you develop an application that uses a specific driver, it can connect to the PostgreSQL database server:
In this tutorial, you’ve learned how to connect to the PostgreSQL database server by using various client tools, including psql and pgAdmin.
