How to switch databases in psql?

Question

In MySQL, I used use database_name;

What's the psql equivalent?

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;` — , Jan 18 '21 at 07:33

score 2222 · Accepted Answer · edited Dec 13 '18 at 16:22

2222

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

\connect DBNAME

or in short:

\c DBNAME

edited Dec 13 '18 at 16:22

Erwin Brandstetter

605,456
145
1,078
1,228

answered Oct 16 '10 at 17:12

Will Hartung

115,893
19
128
203

167

+1: This is JUST a psql command, in Postgres itself, there is no way to "switch". In fact, psql isn't "switching" in the MySQL context, just closing one connection and opening another. – rfusca Oct 17 '10 at 16:50
17

So there is no chance to do it with SQL? – Borys Jan 04 '13 at 09:03
11

So can this work among SQL statements in a `.sql` file? e.g. can I have `CREATE DATABASE mydb;` followed by `\connect mydb`? – J86 Jun 11 '18 at 21:10
3

@Ciwan I'm pretty sure you can't include `psql` commands in a SQL script file. – Kenny Evitt Aug 07 '18 at 20:51
4

For "switching" schema, use [`set searchpath=schema_name`](https://www.postgresql.org/docs/12/ddl-schemas.html#DDL-SCHEMAS-PATH). – Hans Ginzel Jun 10 '20 at 08:00
3

@HansGinzel that changes schema not database there is a huge difference! – Peter Dec 11 '20 at 12:48
2

@Peter: MySQL's databases are in fact schemas. So in most cases they should be mapped to a schema in Postgres, rather than a database. – Jan 18 '21 at 07:34
@a_horse_with_no_name while that might be true for mysql its not for postgres. – Peter Jan 18 '21 at 07:37
@Peter: I know that. However the question is about the MySQL equivalent of switching "a database". – Jan 18 '21 at 07:38
1

to connect with username and password `\connect mydb myusername;` – prayagupa Jun 23 '21 at 22:28
3

@J86 Yes, you can use a `CREATE DATABASE mydb;` line followed by a `\connect mydb` line in a `.sql` file, so long as that file is being run by `psql`. (eg. using psql to run: `\i path_to_sql_script.sql`) [I know because I just tried this and it works] – Venryx Nov 21 '22 at 04:02

score 198 · Answer 2 · edited Nov 01 '14 at 05:33

198

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

edited Nov 01 '14 at 05:33

Asclepius

57,944
17
167
143

answered Oct 16 '10 at 17:14

user229044

232,980
40
330
338

score 108 · Answer 3 · answered Oct 16 '10 at 17:14

108

At the PSQL prompt, you can do:

\connect (or \c) dbname

answered Oct 16 '10 at 17:14

Michael Goldshteyn

71,784
24
131
181

score 41 · Answer 4 · answered Aug 02 '21 at 16:19

41

use \c databaseName or \connect databaseName

(Working on psql 13.3)

answered Aug 02 '21 at 16:19

Anis KCHAOU

830
1
11
11

1

This is pretty much a copy of the accepted answer. – Akaisteph7 Aug 09 '23 at 21:36

score 39 · Answer 5 · answered Jul 25 '14 at 12:36

39

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

answered Jul 25 '14 at 12:36

Manel Clos

1,785
2
19
15

score 20 · Answer 6 · answered Sep 13 '18 at 04:51

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.

score 18 · Answer 7 · answered Feb 26 '16 at 07:10

18

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

answered Feb 26 '16 at 07:10

Ammy

369
2
8

score 14 · Answer 8 · answered Oct 22 '15 at 00:52

14

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.

answered Oct 22 '15 at 00:52

Franck Dernoncourt

77,520
72
342
501

score 11 · Answer 9 · edited Jun 11 '18 at 12:13

11

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

\c databaseName

edited Jun 11 '18 at 12:13

MediaVince

479
1
8
13

answered Jun 13 '16 at 08:12

Bilal Mahmood

169
1
3

score 9 · Answer 10 · answered Jul 20 '19 at 04:08

9

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

\connect DBNAME ROLENAME;

or

\c DBNAME ROLENAME;

answered Jul 20 '19 at 04:08

Abhishek

3,304
4
30
44

Lakhani Aliraza · Answer 11 · 2021-06-13T09:08:26.273

7

You can connect using

\c dbname

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

rails dbconsole (You will be redirected to your current ENV database)
? (For POSTGRESQL commands)

or

\h (For SQL commands)
Press Q to Exit

edited Jun 13 '21 at 09:08

answered Jul 06 '19 at 07:55

Lakhani Aliraza

435
6
8

score 5 · Answer 12 · answered Jun 13 '18 at 15:47

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.

score 4 · Answer 13 · answered Jul 25 '20 at 12:55

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

vikash vishwakarma · Answer 14 · 2021-01-18T10:08:19.037

2

  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

edited Jan 18 '21 at 10:08

answered Jan 18 '21 at 07:28

vikash vishwakarma

51
3

score -13 · Answer 15 · edited Mar 24 '22 at 21:52

-13

You can just enter use [dbName] to switch between databases without reentering your password.

edited Mar 24 '22 at 21:52

vvvvv

25,404
19
49
81

answered Mar 24 '22 at 21:51

Kevin Köppe

1

This is not valid syntax in psql (though it is in MySQL, but that's a completely different database. – snakecharmerb Mar 30 '22 at 18:00

How to switch databases in psql?

15 Answers15

Linked

Related