There are three ways to change database in psql: the \c meta-command for interactive sessions, the -d connection-string flag for scripts, and the SQL USE (or fully-qualified names) for code that should not depend on the connection. The reason the search results confuse most people is that the three are not interchangeable — the meta-command does not work in scripts, the connection-string flag does not work interactively without re-entering credentials, and the SQL approach only works if the user has the right per-database grants. The right one depends on whether the team is in a terminal, in a script, or in an application.
This post walks through the three, the gotchas, and the managed Postgres pattern that is the only one most teams will end up using.
Table of contents
- The three ways to change database in psql
- The interactive way: the meta-command
- The script-friendly way: the connection-string flag
- The SQL way: fully qualified names
- The managed Postgres pattern
- The audit pattern: who can connect to which database
- How this fits the rest of the stack
- FAQ
The three ways to change database in psql
The three patterns, in order of how often they bite teams:
- The meta-command
\c target_db(or\connect target_db). This is the interactive pattern. It works in apsqlREPL session, it prompts for a password if needed, and it leaves the session connected to the new database. It does not work in scripts that pipe commands in. - The connection-string flag
-d target_db(or--dbname=target_db). This is the script-friendly pattern. It tellspsqlto connect to a different database on startup. It requires the user to be able to authenticate to the new database. - The SQL way: fully-qualified table names (
SELECT * FROM other_db.public.users) orSET search_path. This is the application pattern. It assumes the user is already connected to a database, and queries the other database through the same connection.
The reason most blog posts confuse the three is that they answer the question “how do I switch databases in psql” with the first answer only. The second and third are the ones teams actually use in scripts and in production code.
The interactive way: the meta-command
The meta-command \c (or its long form \connect) is the standard answer in the official psql documentation and the most-referenced Stack Overflow answer. The form is:
\c target_db
-- or
\c target_db target_user
-- or
\c "postgresql://user:pass@host:port/target_db"The meta-command reconnects the current session to a different database, optionally as a different user, and optionally with a full connection string. If the new database requires a password and the user has not provided one, psql prompts for it.
The gotcha: the meta-command is a client-side operation. It sends a new connection to the server. The server tears down the old session and starts a new one. Any session-level state (temp tables, prepared statements, SET variables) is lost. For a long-running psql session, this is usually fine. For a script, it is usually a problem.
The second gotcha: the meta-command does not work in a script that is piped to psql. A script of SQL commands sent via psql -f script.sql cannot use \c because the meta-commands are client-side, not SQL. The script fails with ERROR: syntax error at or near "\".
The script-friendly way: the connection-string flag
The connection-string flag is the right answer for scripts. The form is:
psql "postgresql://user:pass@host:5432/target_db" -f script.sql
# or
psql -h host -U user -d target_db -f script.sql
# or
psql -d target_db -c "SELECT 1"The -d flag tells psql to connect to target_db on startup. The session is bound to the target database for the entire run. The script’s SQL commands execute against the target database without needing to switch.
The gotcha: the script-friendly pattern requires the user to have permission to connect to target_db. If the user has only been granted access to a specific database (the pattern recommended by least-privilege), the script will fail with FATAL: permission denied for database "target_db".
The second gotcha: the connection-string flag is not the same as \c. The flag is processed at startup, before any SQL runs. The meta-command is processed inside the REPL, after the connection. The two cannot be mixed in the same script run.
The third gotcha: when the script uses PGPASSWORD or a .pgpass file, the script-friendly pattern picks up the credentials automatically. When the script is run in CI, the CI’s secret store injects PGPASSWORD or the URL with the embedded password. When the script is run in a Docker container, the same pattern applies, with the secret passed via env var.
The SQL way: fully qualified names
The SQL way is the right answer for application code that needs to query multiple databases in the same connection. The form is:
SELECT * FROM target_db.public.users LIMIT 10;
-- or
SELECT * FROM target_db.users LIMIT 10; -- requires search_path to include target_dbThe fully-qualified form (database.schema.table) is the unambiguous way to reference a table in a different database. The database.public.users form requires the connection to have access to both databases, which is rare in production.
The gotcha: the fully-qualified form requires the dblink extension or a foreign data wrapper for cross-database queries in older PostgreSQL versions. In PostgreSQL 9.3 and later, the form is supported natively, but the user still needs CONNECT permission on the target database.
The second gotcha: the SQL way is the wrong answer for “I want to switch the connection.” The SQL way says “I want to query another database from this connection.” The two are different. The application pattern is to use a connection pool per database, not to switch connections mid-query.
The managed Postgres pattern
The managed Postgres pattern is the one most teams will end up using. The pattern is: the platform provisions a Postgres instance, the team gets a connection string, the team’s app uses that connection string, and the team never uses psql against a different database.
The reasons this is the right pattern:
- The app’s connection string is the source of truth. The app reads
process.env.DATABASE_URLand connects to the database the platform provisioned. There is no\cin the app, no connection switching, no cross-database queries. - Multiple databases mean multiple connection strings. If the app needs to query two databases, the app uses two connection pools, not one connection with
\ccalls. The two pools are managed by the application code, not by the developer in a REPL. - The platform handles the credential rotation, the network rules, and the connection pooling. The team writes the app, the platform handles the rest. The
psqlREPL is for debugging, not for production access.
The gotcha: the managed Postgres pattern does not give the team a psql REPL by default. The team has to provision one, authenticate to the platform, and connect. The platform usually has a one-click “psql in the browser” or a one-click “psql from your machine” button.
The audit pattern: who can connect to which database
The audit pattern is the same regardless of which way the team uses. The question is: which users can connect to which databases, and why. The standard audit query is:
SELECT datname, datacl FROM pg_database;The datacl column is the access control list for the database. The standard form is user=privileges/grantor. The audit reads the column, identifies which users have CONNECT permission, and confirms the list is what the team expects.
The gotcha: a user with SUPERUSER can connect to any database. The audit needs to flag superusers separately, because the audit of who can connect to which database is moot if a superuser is in the system.
How this fits the rest of the stack
The database pattern is also a cost pattern. The database tier, the storage, the bandwidth, the connection count, the connection pool size, and the read replica count each show up as a line item on the platform bill, and the team’s mental model for the project cost is the sum of those numbers. The right answer is to know the line items before the project ships, not after. The RunxBuild hosting calculator is the right place to do that exercise — pick the database tier, the storage, the connection count, the replica count, and the bandwidth, and the calculator shows what the database actually costs at the team’s actual usage.
Useful related references:
FAQ
How do I change database in psql?
The three ways: the meta-command \c target_db for interactive sessions, the connection-string flag -d target_db for scripts, and the SQL way (fully-qualified table names) for code that queries multiple databases from one connection. The right one depends on whether the team is in a terminal, in a script, or in an application.
Does \c work in a psql script?
No. The \c meta-command is a client-side operation, not a SQL command. When a script is piped to psql (via -f script.sql or stdin), the meta-commands are not interpreted. The script must use the -d connection-string flag at startup, or it must use fully-qualified table names if it is already connected.
Can I switch databases in a single psql session without reconnecting?
Not through the psql REPL. The \c meta-command tears down the old session and starts a new one. Session-level state (temp tables, prepared statements, SET variables) is lost. If the team’s workflow needs the state preserved, the answer is multiple connections, not a single connection with switches.
How do I switch databases in a CI script?
The connection-string flag at startup. The form is psql "$DATABASE_URL" -f script.sql, where $DATABASE_URL is the URL for the target database. The CI’s secret store injects the URL, the script runs against the target database, and the connection ends.
How do I switch databases in a Docker container?
The connection-string flag at startup, the same as a CI script. The Docker container reads DATABASE_URL from the environment, the entrypoint script runs psql "$DATABASE_URL" -f init.sql, and the container exits. The platform’s runtime injects the URL via the secret store, not via the Dockerfile.
Can I use \c to connect as a different user?
Yes. The form is \c target_db target_user, or \c "postgresql://user:pass@host:port/target_db" for the full URL. The meta-command reconnects with the new user and prompts for the password if needed. The pattern is the same as the database switch — client-side, reconnects, loses session state.
How do I find out which users can connect to a database?
The pg_database.datacl column is the ACL for the database. The has_database_privilege(rolname, datname, 'CONNECT') function returns true or false for a specific user. The standard audit combines both: list the ACL for the database, list the users with CONNECT permission, and flag any superuser that bypasses the ACL.