PostgreSQL - Select Database

PostgreSQL - Connecting to a Database

To work with your data in PostgreSQL, you first need to establish a connection to a specific database. You can do this using the command-line interface (psql), modern GUI tools, or directly from your application code using a connection string.

Method 1: Using the psql Command-Line Interface

psql is the powerful, interactive terminal for PostgreSQL. It’s an essential tool for any developer or administrator.

Connecting Directly from Your Shell

You can connect to a specific database directly when you start psql by providing the database name as the last argument. Other common flags include -U for user, -h for host, and -p for port.

# Connect to the 'testdb' database as user 'postgres' on the local machine
psql -U postgres -d testdb

# You will be prompted for a password
Password for user postgres:

# Successful connection prompt (version may vary)
psql (16.0)
Type "help" for help.

testdb=>

The prompt testdb=> indicates you are now connected to the testdb database.

Switching Databases from within psql

If you are already inside psql, you can list all available databases with the \l command and switch to a different one with the \c [database_name] command.

postgres=> \l
                                 List of databases
   Name    |  Owner   | Encoding | Collate |  Ctype  |   Access privileges
-----------+----------+----------+---------+---------+-----------------------
 postgres  | postgres | UTF8     | C.UTF-8 | C.UTF-8 |
 template0 | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres ...
 template1 | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres ...
 testdb    | postgres | UTF8     | C.UTF-8 | C.UTF-8 |
(4 rows)

postgres=> \c testdb
You are now connected to database "testdb" as user "postgres".
testdb=>

To exit psql at any time, use the \q command.

Method 2: Modern Connection Methods

Connection URIs

A connection URI (Uniform Resource Identifier) is a standardized string format for specifying connection details. It is widely used by programming languages, libraries (like Node.js’s pg or Python’s psycopg2), and GUI tools because it encapsulates all necessary information in a single string.

The format is:

postgresql://[user[:password]@]host[:port]/dbname[?options]

Example:

postgresql://postgres:mysecretpassword@localhost:5432/testdb

GUI Database Tools

For a more visual and user-friendly experience, many developers use Graphical User Interface (GUI) tools to connect to and manage their databases. These tools provide features like schema browsing, a query editor with syntax highlighting, and data visualization.

Popular GUI tools for PostgreSQL include:

• pgAdmin: The official open-source management tool for PostgreSQL.
• DBeaver: A free, universal database tool that supports many different database systems, including PostgreSQL.
• DataGrip: A powerful, commercial database IDE from JetBrains.
• Postico (macOS): A clean and modern native client for PostgreSQL on macOS.

All these tools will ask for the same basic connection parameters: Host, Port, Database Name, User, and Password.