568

I am beginner to PostgreSQL.

I want to connect to another database from the query editor of Postgres - like the USE command of MySQL or MS SQL Server.

I found \c databasename by searching the Internet, but its runs only on psql. When I try it from the PostgreSQL query editor I get a syntax error.

I have to change the database by pgscripting. Does anyone know how to do it?

Braiam
  • 1
  • 11
  • 47
  • 78
sam
  • 5,793
  • 2
  • 14
  • 9
  • Possible duplicate of [How to switch databases in psql?](https://stackoverflow.com/questions/3949876/how-to-switch-databases-in-psql) – AlikElzin-kilaka Sep 13 '18 at 04:46
  • Another option is to directly connect to the schema. Example: `sudo -u postgres psql -d my_database_name`. [Source](https://stackoverflow.com/a/52306845/435605) – AlikElzin-kilaka Sep 13 '18 at 04:52
  • In pgAdmin, you can right-click on your database and select "Query Tool" to run queries on that database. – yoyo Apr 16 '21 at 22:14
  • 1
    if you create a table, put a database prefix before the table name : CREATE TABLE database.tablename; – Eric Lavoie Sep 01 '21 at 00:35

7 Answers7

591

When you get a connection to PostgreSQL it is always to a particular database. To access a different database, you must get a new connection.

Using \c in psql closes the old connection and acquires a new one, using the specified database and/or credentials. You get a whole new back-end process and everything.

Example:

yourUser=# \c newDatabaseName
You are now connected to database "newDatabaseName" as user "yourUser".
artfulrobot
  • 20,637
  • 11
  • 55
  • 81
kgrittn
  • 18,113
  • 3
  • 39
  • 47
  • Thanks kgrittn for your valuable guidance.Can you tell me how I can make new connection to database and close previous by using pgscript query? – sam Apr 27 '12 at 06:41
  • I'm not familiar with pgscript. If that's a language in which you write functions, the answer is that it can't be done. Maybe you should consider putting tables in different *schemas* instead of different *databases*? – kgrittn Apr 27 '12 at 12:40
  • 6
    A query can't change the database in PostgreSQL. – kgrittn Apr 28 '12 at 01:04
  • 17
    If I'm not mistaken, databases in MySQL are more akin to schemas in PostgreSQL -- you can switch between those, but DBs in PostgreSQL are a whole different ballgame. – mpen May 10 '12 at 03:40
  • This seems like a wise limitation/design in terms of security. – northben Apr 25 '15 at 01:35
  • That's not true always. Right now I'm connected to a server without being inside any database. – Maf Apr 29 '20 at 20:34
  • 1
    If you have a PostgreSQL connection, it is always associated with a database. To find out which one, send the database this query: `select current_database()` – kgrittn Aug 19 '20 at 15:24
290

You must specify the database to use on connect; if you want to use psql for your script, you can use "\c name_database"

user_name=# CREATE DATABASE testdatabase; 
user_name=# \c testdatabase 

At this point you might see the following output

You are now connected to database "testdatabase" as user "user_name".
testdatabase=#

Notice how the prompt changes. Cheers, have just been hustling looking for this too, too little information on postgreSQL compared to MySQL and the rest in my view.

Eugene
  • 473
  • 1
  • 9
  • 15
43

In pgAdmin you can also use

SET search_path TO your_db_name;

Bart De Boeck
  • 642
  • 6
  • 9
15

The basic problem while migrating from MySQL I faced was, I thought of the term database to be same in PostgreSQL also, but it is not. So if we are going to switch the database from our application or pgAdmin, the result would not be as expected. As in my case, we have separate schemas (Considering PostgreSQL terminology here.) for each customer and separate admin schema. So in application, I have to switch between schemas.

For this, we can use the SET search_path command. This does switch the current schema to the specified schema name for the current session.

example:

SET search_path = different_schema_name;

This changes the current_schema to the specified schema for the session. To change it permanently, we have to make changes in postgresql.conf file.

VPK
  • 3,010
  • 1
  • 28
  • 35
8
set search_path = 'schema name here'

while connecting to the postgres, you have to opt for default database to connect. If you have nothing, you can use 'postgres' as default.

You can use dbeaver to connect to postgres. UI is good

7

Use this commad when first connect to psql

=# psql <databaseName> <usernamePostgresql>
Sukma Saputra
  • 1,539
  • 17
  • 32
6

PgAdmin 4, GUI Tool: Switching between databases

  1. In the PgAdmin Browser on the left hand side, right click on the database you are willing to switch to.
  2. Select a QueryTool from the drop down menu (or any other option that you need, I will stick with the QueryTool for now).
  3. You will see the QueryTool in the PgAdmin window, and on top you will see the active database and the role name.
  4. Now you can write queries against the chosen database.
  5. You can open multiple QueryTools for multiple database, and work with them as you do with your graphical text editor.

In order to be sure that you are querying the proper database, issue the following query:

SELECT session_user, current_database();
alv2017
  • 752
  • 5
  • 14