Switching Databases with PostgreSQL's `psql`

When working with databases, it is often necessary to switch between different database environments. For MySQL users, switching databases within a session can be done using the command USE database_name;. However, when you transition to PostgreSQL and use its interactive terminal tool psql, the method changes slightly.

Understanding psql

psql is the command-line interface for interacting with PostgreSQL databases. It’s a powerful tool that allows users to execute queries, manage databases, and perform various administrative tasks directly from the terminal.

When you open psql, it connects you to the default database specified during the connection. However, there are times when you need to switch to another database within the same session without exiting and reconnecting through different means.

Switching Databases in psql

To change databases in psql while maintaining your current session, you can utilize its built-in meta-commands. Meta-commands are special commands recognized by psql that provide additional functionality beyond standard SQL queries. These start with a backslash (\) and perform various actions within the client.

The equivalent of MySQL’s USE database_name; in psql is achieved using the \connect meta-command, often shortened to \c. Both forms work interchangeably:

\connect database_name

Or, using its short form:

\c database_name

Examples

Suppose you have a session connected to the postgres database but need to switch to a database named sales_data. Here’s how you can do it:

1. Connect to a Different Database:
   At your current psql prompt, enter:

   \c sales_data
   

2. Verify the Connection:
   To ensure that you’ve switched databases successfully, you can check the database name at the top of the psql prompt, which should now display as (sales_data). Alternatively, use:

   SELECT current_database();
   

   This query returns the name of the currently connected database.

Connecting to a Database on Launch

While switching databases is useful during an active session, sometimes you want to specify your target database when launching psql itself. You can do this directly in the command line:

psql -d sales_data

This connects you directly to sales_data without needing any further commands once inside.

Scripting with Database Connections

For automation or scripting purposes, you might want to execute a series of SQL commands on a specific database right from the start. This is particularly useful in deployment scripts or automated testing environments:

sudo -u postgres psql -d sales_data -c "CREATE SCHEMA analytics AUTHORIZATION admin;"

This command logs into PostgreSQL as postgres, connects directly to the sales_data database, and executes a single SQL statement to create a new schema.

Best Practices

• Use Short Forms for Convenience: When working interactively within psql, using \c is often quicker than typing out the full \connect.

• Specify Database in Scripts: For automated tasks or scripts, always specify your target database with -d to avoid any unnecessary confusion about which database commands will affect.

• Check Current Database Regularly: Especially when working within shared environments or using multiple databases, confirm your current database connection to prevent accidental data modifications.

By mastering these simple but powerful psql techniques, you can efficiently manage and switch between multiple PostgreSQL databases seamlessly during your session.