“use database_name” command in PostgreSQL

Question

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?

Answer 1

In pgAdmin you can also use

SET search_path TO your_db_name;

Answer 2

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.

Answer 3

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.

Answer 4

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.

Answer 5

Use this commad when first connect to psql

=# psql <databaseName> <usernamePostgresql>