DEV Community

Cover image for Switch Databases in psql: The \connect Command (2026)
Mahdi BEN RHOUMA
Mahdi BEN RHOUMA

Posted on Originally published at iloveblogs.blog

Switch Databases in psql: The \connect Command (2026)

#postgres #psql #database #supabase

The muscle-memory trap

You came from MySQL. You opened psql, ran \l to list databases, and typed
USE mydb;. psql replied with:

mydb=> use mydb;
ERROR:  syntax error at or near "use"
LINE 1: use mydb;
Enter fullscreen mode Exit fullscreen mode

There is no USE in PostgreSQL, and there is no in-session database switch.
Each PostgreSQL database is an isolated connection target. Switching databases
means opening a new connection, and psql gives you one meta-command for it:
\connect, abbreviated \c.

This is one of the highest-traffic psql questions on Stack Overflow (the
"switch databases in psql" thread has 1.3M+ views) precisely because of that
MySQL muscle memory.

The command 

\c dbname
Enter fullscreen mode Exit fullscreen mode

or the long form:

\connect dbname
Enter fullscreen mode Exit fullscreen mode

psql prints the new connection prompt:

postgres=> \c mydb
You are now connected to database "mydb" as user "postgres".
mydb=>
Enter fullscreen mode Exit fullscreen mode

The prompt changes to reflect the database you are in. Your current session
state — temporary tables, SET variables, prepared statements — is gone,
because it is a brand new connection.

Switch user, host, or port at the same time

\c accepts a full connection spec. You can switch to a different database
and a different user in one command:

\c dbname newuser
\c dbname newuser newhost 5433
Enter fullscreen mode Exit fullscreen mode

When you omit the user, psql reuses the current one. When you omit host and
port, it reuses the current connection's. If the new database needs a password,
psql prompts for it (or uses the PGPASSWORD env var / ~/.pgpass file).

The transaction gotcha

You cannot switch databases inside an open transaction:

mydb=> BEGIN;
BEGIN
mydb=*# \c otherdb
FATAL:  terminating connection due to administrator command
could not connect to server: ...
Enter fullscreen mode Exit fullscreen mode

Some psql versions phrase it as "there is a transaction in progress". Either
way the rule is the same: finish the transaction first.

mydb=*# COMMIT;   -- or ROLLBACK
COMMIT
mydb=# \c otherdb
You are now connected to database "otherdb".
Enter fullscreen mode Exit fullscreen mode

This exists because switching databases silently drops the connection — and
PostgreSQL will not let uncommitted work vanish without an explicit decision.

Connect at launch instead

If you know the database up front, skip \c entirely and connect at launch:

psql -d mydb
psql -d mydb -U app_user -h localhost -p 5432
Enter fullscreen mode Exit fullscreen mode

or with a connection string (the form Supabase gives you):

psql "postgresql://user:pass@host:5432/mydb"
Enter fullscreen mode Exit fullscreen mode

For the common "list databases, pick one, connect" flow, the sequence is:

\l              -- list databases
\c target_db    -- connect to the one you want
\dt             -- list tables in the new database
Enter fullscreen mode Exit fullscreen mode

Once you are in, the tables are not in a single list you can scan without
context — see show tables in PostgreSQL
for the full \dt, \d, and information_schema walkthrough, and the
PostgreSQL DESCRIBE TABLE equivalent
for inspecting columns.

Exit psql

While we are on meta-commands: to leave psql, it is \q (or Ctrl+D):

mydb=> \q
Enter fullscreen mode Exit fullscreen mode

Not exit, not quit — though recent psql versions accept those as aliases.
\q works everywhere.

Supabase: connect to a specific project

Supabase exposes a Postgres database per project. From the dashboard:

Project Settings → Database → Connection string → URI

Copy it (the pooling variant, port 6543, is what you want for CLI work):

psql "postgresql://postgres.[ref]:[password]@aws-0-[region].pooler.supabase.com:6543/postgres"
Enter fullscreen mode Exit fullscreen mode

You land in the postgres database — the only user database. There is rarely
a reason to \c to another database on Supabase; you switch between schemas
(public, auth, storage) with SET search_path, not databases:

SET search_path TO public, auth;
SELECT * FROM auth.users;
Enter fullscreen mode Exit fullscreen mode

If you need to reset the password on that connection, the
change PostgreSQL user password guide
covers the ALTER ROLE flow that works on Supabase too.

Common mistakes

  • Typing USE dbname. There is no USE in psql. It is \c dbname.
  • Switching inside a transaction. COMMIT or ROLLBACK first.
  • Forgetting the prompt change. The prompt shows the current database; if your queries hit the wrong tables, check it.
  • Mixing up database and schema. On PostgreSQL (and Supabase) you usually want to switch schema with SET search_path, not database with \c.
  • Connecting on port 5432 to Supabase. That is the direct connection; use 6543 (pooler) unless you need a feature PgBouncer does not support.

Related Articles

Originally published at https://www.iloveblogs.blog

Top comments (0)