Postgresql: show databases, show tables, show columns

Hi all, In this article you will know some very very basic commands about postgresql. When i started learning postgresql, I found bit strange when “show databases”, “show tables”, and “show columns” command did not work. I was using mysql and I am still using mysql but it is just matter of learning one more database as postgresql started becomming popular now. So.. If you have question in your mind how to list databases, tables and colums in postgresql database then I have answer for you.

First of all to connect to postgresql you need to use :
psql -d “database name” -h “hostname OR IP” -U “user name” command. This will prompt you to enter password.
Note: By default you can not connect to postgresql with user name root.

  1. To list databases aaccessible to user you connected with
    mysql: SHOW DATABASES
    postgresql: \l
    postgresql: SELECT datname FROM pg_database;
  2. To list tables in your database 
    mysql: SHOW TABLES
    postgresql: \d
    postgresql: SELECT table_name FROM information_schema.tables WHERE table_schema = ‘public’;
  3. To list columns in particular table / schema use : 
    mysql: SHOW COLUMNS
    postgresql: \d table
    postgresql: SELECT column_name FROM information_schema.columns WHERE table_name =’table‘;
  • Login to post comments

Leave a Reply

Your email address will not be published. Required fields are marked *