PostgreSQL Select Database

Overview

In PostgreSQL, you need to connect to a specific database before you can perform operations on it. This chapter explains how to select and switch between databases.

Selecting Database When Connecting

Using psql

# Connect to specified database
psql -d myapp -U postgres

# Full connection parameters
psql -h localhost -p 5432 -d myapp -U postgres

# Using connection string
psql "postgresql://postgres@localhost/myapp"

Connection String Format

postgresql://[user[:password]@][host][:port][/database][?param=value]

Example:

psql "postgresql://postgres:password@localhost:5432/myapp"

Switching Databases

Using \c Command

In psql, use \c or \connect command to switch databases:

-- Switch to myapp database
\c myapp

-- Switch database and specify user
\c myapp postgres

-- Switch database, user, host, and port
\c myapp postgres localhost 5432

Switch Example

postgres=# \c myapp
You are now connected to database "myapp" as user "postgres".
myapp=#

View Current Database

Using psql Commands

-- Show current database
SELECT current_database();

-- Show connection info
\conninfo

Output Example

myapp=# SELECT current_database();
 current_database
------------------
 myapp
(1 row)

myapp=# \conninfo
You are connected to database "myapp" as user "postgres" on host "localhost" at port "5432".

List All Databases

Using psql Commands

-- List all databases
\l

-- List with details
\l+

Using SQL Query

-- Query database list
SELECT datname, pg_database_size(datname) as size
FROM pg_database
WHERE datistemplate = false;

-- View detailed database information
SELECT
    datname as "Database",
    pg_catalog.pg_get_userbyid(datdba) as "Owner",
    pg_catalog.pg_encoding_to_char(encoding) as "Encoding",
    datcollate as "Collation",
    datctype as "Ctype"
FROM pg_catalog.pg_database
ORDER BY datname;

Selecting Database in Applications

Python (psycopg2)

import psycopg2

# Connect to specified database
conn = psycopg2.connect(
    host="localhost",
    database="myapp",
    user="postgres",
    password="password"
)

Node.js (pg)

const { Pool } = require('pg');

const pool = new Pool({
    host: 'localhost',
    database: 'myapp',
    user: 'postgres',
    password: 'password'
});

Java (JDBC)

String url = "jdbc:postgresql://localhost:5432/myapp";
Connection conn = DriverManager.getConnection(url, "postgres", "password");

Important Notes

1. Connection limitation: Each database connection can only access one database
2. Switching overhead: Frequent database switching increases connection overhead
3. Permission check: Switching databases requires CONNECT privilege on the target database
4. Default database: If no database is specified, usually connects to a database with the same name as the user

Common Issues

Database Does Not Exist

FATAL: database "myapp" does not exist

Solution: Create the database first or connect to another existing database.

Insufficient Permissions

FATAL: permission denied for database "myapp"

Solution: Use a user with permissions or grant CONNECT privilege.