List Databases, Tables, schemas and other basic commands in PostgreSQL

In previous articles we have learned about basics of PostgreSQL, architecture and installation of PostgreSQL on windows
(Read here Introduction to PostgreSQL , Installation of PostgreSQL )
Now we will learn basic commands to administrates the PostgreSQL databases

Login to PostgreSQL
we can login to postgres database using psql with few options like 
database name and postgres user, below we logged in to tech_pg database using user postgreu

$ psql -d tech_pg -U postgreu

psql.bin (9.6.2.7)

Type "help" for help.

tech_pg=# 

Listing the database available
A single PostgreSQL server instance can have multiple databases, each and every database is stored as separate file under its own directory, to list database we can use \l or we can use pg_database table

tech_pg=# \l

                                             List of databases

     Name     |   Owner    | Encoding |   Collate   |    Ctype    | ICU |        Access privileges

--------------+------------+----------+-------------+-------------+-----+----------------------------------

 tech_pg      | postgreu | UTF8     | en_US.UTF-8 | en_US.UTF-8 |     |

 postgres     | postgreu | UTF8     | en_US.UTF-8 | en_US.UTF-8 |     |

template0     | postgreu | UTF8     | en_US.UTF-8 | en_US.UTF-8 |     | =c/postgreu                   +

              |            |          |             |             |     | postgreu=CTc/postgreu

 template1    | postgreu | UTF8     | en_US.UTF-8 | en_US.UTF-8 |     | =c/ postgreu                   +

              |            |          |             |             |     | postgreu=CTc/postgreu

(4 rows)

tech_pg=#

tech_pg=# select datname from pg_database;

   datname

--------------

 postgres

 tech_pg

 template1

 template0

 (4 rows)

tech_pg=#

Connect to database (Switch Database)

By default PostgreSQL server instance has 3 databases i.e. postgres, template1 and template0

one can connect to database from postgres command line tool and switch to other database using \connect or \c

tech_pg=# \c postgres

You are now connected to database "postgres" as user "postgreu".

postgres=#

postgres=# \connect tech_pg

You are now connected to database "tech_pg" as user "postgreu".

tech_pg=#

Checking Current Database

We can check the name of the database in which we are currently logged in using different methods, i.e. using \c, \conninfo, select * from current_catalog and current_database() function. it has been illustrated below 

tech_pg-# \c tech_pg

You are now connected to database "tech_pg" as user "postgreu".

tech_pg-#

tech_pg=# \conninfo

You are connected to database "tech_pg" as user "postgreu" via socket in "/tmp" at port "5444".

tech_pg=#

tech_pg=# \c

You are now connected to database "tech_pg" as user "postgreu".

tech_pg=#

tech_pg=# select * from current_catalog;

 current_database

------------------

 tech_pg

(1 row)

tech_pg=# select current_database() ;

 current_database

------------------

 tech_pg

(1 row)

tech_pg=#

Listing the Tables
one can simply list the tables created under specific database using \dt command, in Below example we can see there is no table present in database, if any present then it will list out with details.

tech_pg=# select current_database() ;

 current_database

------------------

 tech_pg

(1 row)

tech_pg=# \dt

No relations found.

tech_pg=#

Get list of all Schema
using command \dn, one can simply list out all the schema in postgres

tech_pg=# \dn

         List of schemas

        Name        |   Owner

--------------------+------------

 postgreu           | postgreu

 dbms_job_procedure | postgreu

 pgagent            | postgreu

 public             | postgreu

(4 rows)

One can use information_schema.schemata view to list out all the schemas in current database 

tech_pg=# select catalog_name,schema_name,schema_owner from information_schema.schemata;

 catalog_name| schema_name    | schema_owner

-------------+--------------------+--------------

 tech_pg | pg_toast           | postgreu

 tech_pg | pg_temp_1          | postgreu

 tech_pg | pg_toast_temp_1    | postgreu

 tech_pg | pg_catalog         | postgreu

 tech_pg | public             | postgreu

 tech_pg | information_schema | postgreu

 tech_pg | sys                | postgreu

 tech_pg | dbo                | postgreu

(8 rows)

Comment for feedback or in case need additional information, share the post if you like it.