How to switch databases in psql?

Question

In MySQL, I used use database_name;

What's the psql equivalent?

Scottie

OK, so this is about psql, the front-end for PostgreSQL? – Flatfish

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; – Tenishatenn

schemas are generally under databases. – Bayadere

Answer 1

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

\connect DBNAME

or in short:

\c DBNAME

Appeasement

+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. – Reprobate

So there is no chance to do it with SQL? – Panpipe

So can this work among SQL statements in a .sql file? e.g. can I have CREATE DATABASE mydb; followed by \connect mydb? – Pontiff

@Ciwan I'm pretty sure you can't include psql commands in a SQL script file. – Tanjatanjore

For "switching" schema, use set searchpath=schema_name. – Someone

@HansGinzel that changes schema not database there is a huge difference! – Swallowtail

@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. – Tenishatenn

@a_horse_with_no_name while that might be true for mysql its not for postgres. – Swallowtail

@Peter: I know that. However the question is about the MySQL equivalent of switching "a database". – Tenishatenn

to connect with username and password \connect mydb myusername; – Joappa

@Pontiff 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] – Fong

Answer 2

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

Analiese

Answer 3

At the PSQL prompt, you can do:

\connect (or \c) dbname

Sunflower

Answer 4

use \c databaseName or \connect databaseName

(Working on psql 13.3)

Guile

This is pretty much a copy of the accepted answer. – Myrtice

Answer 5

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

Ex

Answer 6

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.

Schroth

Answer 7

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

Madoc

Answer 8

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.

Frank

Answer 9

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

\c databaseName

Vaillancourt

Answer 10

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

\connect DBNAME ROLENAME;

or

\c DBNAME ROLENAME;

Amphibology

Answer 11

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

2. \h (For SQL commands)

3. Press Q to Exit

Pothook

Answer 12

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

Ostrich

Answer 13

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.

Googly

Answer 14

  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

Trilobite

Answer 15

SET schema 'schemaName';

The correct translation of 'use databasename;' from mysql to postgreSQL has to consider that in postgreSQL you have also schemas. Usually your connection string has alreday defined the databasename, you have to specify the default schema.

After setting the schema name you can execute query like:

SELECT * from tableName;

without repeating the schemaName like here:

SELECT * from schemaName.tableName;

Drumlin

schema in postgres is NOT the same as a database. – Joshuajoshuah

Answer 16

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

Mosenthal

This is not valid syntax in psql (though it is in MySQL, but that's a completely different database. – Peloria