112

I'm using PostgreSql version : 

postgres=# select version();
                           version
-------------------------------------------------------------
 PostgreSQL 9.2.4, compiled by Visual C++ build 1600, 64-bit
(1 row)

i had connected to a database from postgres=# to newdb=#.... Now i'm in newdb=# Database i want to disconnect it and go back to postgres=# database ....

How to do this ?

I have tried with disconnect newdb;

but its giving erroe as::

postgres=# create database newdb;
CREATE DATABASE
postgres=# \c newdb;
WARNING: Console code page (437) differs from Windows code page (1252)
         8-bit characters might not work correctly. See psql reference
         page "Notes for Windows users" for details.
You are now connected to database "newdb" as user "postgres".
newdb=# disconnect newdb;
ERROR:  syntax error at or near "disconnect"
LINE 1: disconnect newdb;
        ^
newdb=#

it isnt working is there any other way to do this or am i wrong in anything!!

09Q71AO534
  • 4,300
  • 13
  • 44
  • 68
  • I know it seems a bit obvious, but have you checked the [`psql` documentation](http://www.postgresql.org/docs/current/static/app-psql.html)? A hint - there's no `disconnect` SQL or `psql` command. – Milen A. Radev Jul 31 '13 at 07:18
  • There is no such thing as a "default database" in Postgres. You need to explictely connect to one database using `\c` –  Jul 31 '13 at 07:27
  • Is that what happens cant we use [DISCONNECT documentation](http://www.postgresql.org/docs/9.1/static/ecpg-sql-disconnect.html) for getting out from the connection @a_horse_with_no_name – 09Q71AO534 Jul 31 '13 at 07:30
  • in it there is a statement that it closes a connection_name but its not working in this senario..is the way of my understanding is wrong !! @a_horse_with_no_name – 09Q71AO534 Jul 31 '13 at 07:31
  • 1
    Then What is the[Documentation which i Found](http://www.postgresql.org/docs/9.1/static/ecpg-sql-disconnect.html) mean @Milen A. Radev – 09Q71AO534 Jul 31 '13 at 07:34
  • Read the title of the chapter that you linked. `disconnect` is a statement for "*Embedded SQL Commands*" (in a `C` program) which is also obvious from the example on the bottom of the page. –  Jul 31 '13 at 07:39
  • 1
    thank u :-) so there is no other way to get out from the database `\q` `quits` only..or else we have to use `\c` to `connect` for a newdb .. we cant use disconnect in `PostgreSql` @a_horse_with_no_name – 09Q71AO534 Jul 31 '13 at 07:49
  • http://stackoverflow.com/questions/4483139/php-how-do-i-connect-to-postgresql-without-specifying-database-name/4483468#4483468 – Milen A. Radev Jul 31 '13 at 08:46

2 Answers2

125

It's easy, just look the example.

--my databases

postgres=# \l
                               List of databases
   Name    |  Owner   | Encoding | Collate | Ctype |     Access privileges     
-----------+----------+----------+---------+-------+---------------------------
 francs    | postgres | UTF8     | C       | C     | =Tc/postgres             +
           |          |          |         |       | postgres=CTc/postgres    +
           |          |          |         |       | francs=C*T*c*/postgres   +
           |          |          |         |       | select_only=c/francs
 postgres  | postgres | UTF8     | C       | C     | 
 source_db | postgres | UTF8     | C       | C     | =Tc/postgres             +
           |          |          |         |       | postgres=CTc/postgres    +
           |          |          |         |       | source_db=C*T*c*/postgres
 template0 | postgres | UTF8     | C       | C     | =c/postgres              +
           |          |          |         |       | postgres=CTc/postgres
 template1 | postgres | UTF8     | C       | C     | =c/postgres              +
           |          |          |         |       | postgres=CTc/postgres
(5 rows)

--switch to db francs as role francs

postgres=# \c francs francs
You are now connected to database "francs" as user "francs".

--swith to db postgres as role postgres

francs=> \c postgres postgres

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

--disconnect from db

postgres=# \q
francs
  • 8,511
  • 7
  • 39
  • 43
  • 3
    it is right but is there any other way such as using DISCONNECT in SQL [Documentation](http://www.postgresql.org/docs/9.1/static/ecpg-sql-disconnect.html) @ francs – 09Q71AO534 Jul 31 '13 at 07:58
  • 1
    @user2561626: may be no， I'm not sure. – francs Jul 31 '13 at 08:09
  • 4
    \q will quit the session and if I want to connect to some other DB then I'd need to \q and then \c again. So there is quit from database, no disconnect from one active session. – Ayush Oct 31 '17 at 12:22
  • Ah, \q quits the session. damn! was typing quit; – Swatantra Kumar Nov 06 '19 at 10:29
  • I'm confused that why there is a user named "postgres" and also a database named "postgres" , should it be more clear like "user_postgre" and "dbname_postgre"? – Archsx Nov 19 '22 at 02:12
58

There is not a 'disconnect' in psql. Instead of disconnecting from your newdb database you connect with the default postgres database.

Create the new database and connect to it:

postgres=# create database newdb;
CREATE DATABASE    
postgres=# \c newdb
You are now connected to database "newdb" as user "postgres".
newdb=#

List the number of connections on newdb:

newdb=# select datname,numbackends from  pg_stat_database where datname='newdb';
 datname | numbackends
---------+-------------
 newdb   |           1

Now, instead of disconnecting, just connect with the default postgres database.

newdb=# \c postgres
You are now connected to database "postgres" as user "postgres".
postgres=#

Now there are no connections on newdb:

postgres=# select datname,numbackends from  pg_stat_database where datname='newdb';
 datname | numbackends
---------+-------------
 newdb   |           0
Hawkez
  • 673
  • 6
  • 7