How to switch databases in psql?

1224
votes

In MySQL, I used use database_name;

What's the psql equivalent?

13
postgresqlpsql
OK, so this is about psql, the front-end for PostgreSQL? – Peter Mortensen
A MySQL "database" is in fact a schema. Therefor in most cases, MySQL's "databases" would better be mapped to schemas in Postgres anyway. And if that is done, you can change the current schema using set schema 'schema_name'; or set search_path to schema_name; – a_horse_with_no_name

13 Answers

1860
votes

In PostgreSQL, you can use the \connect meta-command of the client tool psql:

\connect DBNAME

or in short:

\c DBNAME
192
votes

You can connect to a database with \c <database> or \connect <database>.

103
votes

At the PSQL prompt, you can do: 

\connect (or \c) dbname
35
votes

You can select the database when connecting with psql. This is handy when using it from a script:

sudo -u postgres psql -c "CREATE SCHEMA test AUTHORIZATION test;" test
16
votes

\l for databases \c DatabaseName to switch to db \df for procedures stored in particular database

12
votes

Using psql's meta-command \c or \connect [ dbname [ username ] [ host ] [ port ] ] | conninfo (see documentation).

Example: \c MyDatabase

Note that the \c and \connect meta-commands are case-sensitive.

10
votes

Though not explicitly stated in the question, the purpose is to connect to a specific schema/database.

Another option is to directly connect to the schema. Example:

sudo -u postgres psql -d my_database_name

Source from man psql:

-d dbname
--dbname=dbname
   Specifies the name of the database to connect to. This is equivalent to specifying dbname as the first non-option argument on the command line.

   If this parameter contains an = sign or starts with a valid URI prefix (postgresql:// or postgres://), it is treated as a conninfo string. See Section 31.1.1, “Connection Strings”, in the
   documentation for more information.
8
votes

Use below statement to switch to different databases residing inside your postgreSQL RDMS

\c databaseName
5
votes

You can also connect to a database with a different ROLE as follows. 

\connect DBNAME ROLENAME;

or 

\c DBNAME ROLENAME;
4
votes

If you want to switch to a specific database on startup, try

/Applications/Postgres.app/Contents/Versions/9.5/bin/psql vigneshdb;

By default, Postgres runs on the port 5432. If it runs on another, make sure to pass the port in the command line.

/Applications/Postgres.app/Contents/Versions/9.5/bin/psql -p2345 vigneshdb;

By a simple alias, we can make it handy.

Create an alias in your .bashrc or .bash_profile

function psql()
{
    db=vigneshdb
    if [ "$1" != ""]; then
            db=$1
    fi
    /Applications/Postgres.app/Contents/Versions/9.5/bin/psql -p5432 $1
}

Run psql in command line, it will switch to default database; psql anotherdb, it will switch to the db with the name in argument, on startup.

4
votes

Listing and Switching Databases in PostgreSQL When you need to change between databases, you’ll use the \connect command, or \c followed by the database name as shown below:

postgres=# \connect database_name
postgres=# \c database_name

Check the database you are currently connected to.

SELECT current_database();

PostgreSQL List Databases

postgres=# \l
 postgres=# \list
1
votes
  Connect to database:

  Method 1 : enter to db : sudo -u postgres psql

  Connect to db : \c dbname

  Method 2 : directly connect to db : sudo -u postgres psql -d my_database_name
1
votes

You can connect using

\c dbname

If you would like to see all possible commands for POSTGRESQL or SQL follow this steps :

  1. rails dbconsole (You will be redirected to your current ENV database)

  2. ? (For POSTGRESQL commands)

or

  1. \h (For SQL commands)

  2. Press Q to Exit