Site icon JD Bots

How to Switch Databases in psql

Featured card titled Switch Databases in psql, with a terminal window showing \c sales, the sales=# prompt, \c support and the support=# prompt.

Switch between databases in psql.

In psql you’re always connected to one database, and its name is part of the prompt. \c moves you to another database without leaving psql.

Every command and output on this page is real, from PostgreSQL 19 on macOS.

Syntax

Syntax (psql)

\c dbname

Syntax (Terminal)

psql -d dbname

Both are described in the psql documentation.

Step 1: Create Two Example Databases

To switch, you need two databases. This guide creates sales and support; skip this step to use your own. Connect with psql, as Part 2 of the PostgreSQL Tutorial shows, then run:

postgres=#

CREATE DATABASE sales;
CREATE DATABASE support;

Output

CREATE DATABASE
CREATE DATABASE

Step 2: Switch to Another Database with \c

postgres=#

\c sales

Output

You are now connected to database "sales" as user "postgres".

The prompt now starts with sales=#: every command you type runs in the sales database.

Step 3: Check Which Database You’re In

sales=#

SELECT current_database();

Output

 current_database
------------------
 sales
(1 row)

Step 4: Switch Again

sales=#

\c support

Output

You are now connected to database "support" as user "postgres".

psql didn’t ask for the password again: it reuses it when only the database changes.

Step 5: Start psql in the Database You Want

To skip the switch, name the database when you start psql in Terminal:

Terminal

psql -U postgres -d sales

Output

Password for user postgres:
psql (19.0)
Type "help" for help.

sales=#

Clean Up

You can’t drop the database you’re connected to, so connect to postgres first, then remove the two example databases:

support=#

\c postgres

postgres=#

DROP DATABASE sales;
DROP DATABASE support;

Output

DROP DATABASE
DROP DATABASE

Warning: DROP DATABASE deletes the database and everything in it at once. There’s no undo, so check the name before you press Return.

Common Mistakes

MistakeWhat happensDo this instead
Typing MySQL’s USE sales;ERROR: syntax error at or near "USE"Use \c sales.
Switching to a database that doesn’t existconnection to server on socket "/tmp/.s.PGSQL.5432" failed: FATAL: database "nosuch" does not exist Previous connection keptCheck the name with \l. psql keeps you in the database you were in.
Dropping the database you’re connected toERROR: cannot drop the currently open databaseRun \c postgres first.

FAQ

Can I use two databases in one query?

No. The PostgreSQL documentation says it isn’t possible to access more than one database per connection. For data you use together, keep it in one database, in separate tables or schemas.

What’s the difference between \c and \connect?

None. \c is the short name of \connect.

Conclusion

Type \c and a database name to switch databases in psql, and check the prompt or SELECT current_database(); to see where you are. Start psql with -d to land in a database straight away.

Additional Resources

Related

Postgres, PostgreSQL and the Slonik Logo are trademarks or registered trademarks of the PostgreSQL Community Association of Canada, and used with their permission.

Exit mobile version