PostgreSQL Connect Another DB

we have already created a database in our previous chapter. You can select database using either of the following methods:

1. OS Command Prompt
2. SQL Prompt

1.OS Command Prompt:

You can check available database list using "psql -l", i.e., :

[postgres@r1 ~]$ psql -l
Password: 
                                  List of databases
    Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
------------+----------+----------+-------------+-------------+-----------------------
 account    | u1       | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 musicdb    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 postgres   | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 sales      | u2       | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 temp0copy  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 temp0copy2 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres     
     +
            |          |          |             |             | postgres=CTc/postgres
 template1  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres     
     +
            |          |          |             |             | postgres=CTc/postgres
(8 rows)

You can select your database from command prompt itself at the time when you login to your database First you need to check which is your  host?  using "hostname". Following is the simple example:

[postgres@r1 ~]$ hostname
r1.localdomain

If you need to log into a Postgres database on a server named "r1", you can use this Postgres login command:

[postgres@r1 ~]$ psql -h r1 -U u2 -d sales
Password for user u2: 
psql.bin (9.3.14)
Type "help" for help.

No entry for terminal type "xterm";
using dumb terminal settings.
sales=> 

If you are logged into the same computer that Postgres is running on you can use the following psql login command, specifying the database (postgres) and username (postgres)

[postgres@r1 ~]$ psql -U u2 -d account;
Password for user u2: 
psql.bin (9.3.14)
Type "help" for help.

No entry for terminal type "xterm";
using dumb terminal settings.

2.SQL Prompt:

You can check available database list using \l, i.e., backslash el command as follows:

sales=> \l
                                  List of databases
    Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
------------+----------+----------+-------------+-------------+-----------------------
 account    | u1       | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 musicdb    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 postgres   | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 sales      | u2       | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 temp0copy  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 temp0copy2 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres     
     +
            |          |          |             |             | postgres=CTc/postgres
 template1  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres     
     +
            |          |          |             |             | postgres=CTc/postgres
(8 rows)

Now type the below command to connect/select a desired database, here we will connect to the "account" database:

sales=> \c account
You are now connected to database "account" as user "u2".

account=> \conninfo
You are connected to database "account" as user "u2" on host "r1" at port "5432".
account=> 

view the postgres "tname" table Using command Line:

[postgres@r1 ~]$ psql -U u2 -d sales -c 'SELECT * FROM tname'
Password for user u2: 
 id | name  
----+-------
  1 | nijam
  2 | abu
  3 | benz
  4 | car
(4 rows)

Connect the "sales" database using command Line password

[postgres@r1 ~]$ psql -d sales -U u1 -W
Password for user u1: 
psql.bin (9.3.14)
Type "help" for help.

No entry for terminal type "xterm";
using dumb terminal settings.

<<< Previews