postgresql logoPostgreSQL is one of the best database engines for an average web project and many who moves to psql from mysql (for example) often ask the following questions: what  is the analog of “show tables” in postgres? or how can I get the list of databases in postgres like “show databases” in mysql? The answers are short:

mysql: SHOW TABLES
postgresql: \d
postgresql: SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';

mysql: SHOW DATABASES
postgresql: \l
postgresql: SELECT datname FROM pg_database;

mysql: SHOW COLUMNS
postgresql: \d table
postgresql: SELECT column_name FROM information_schema.columns WHERE table_name ='table';

mysql: DESCRIBE TABLE
postgresql: \d+ table
postgresql: SELECT column_name FROM information_schema.columns WHERE table_name ='table';

 

29 Comments

 

  1. August 25, 2009  6:49 pm by tulip Reply

    COOL! please add more!

  2. October 22, 2009  10:09 am by BadGirl88 Reply

    While most would agree that this seems to be the endall stratagem, it is only a starting point with endless possibilities. ,

  3. October 23, 2009  9:40 am by Settor17 Reply

    Later, Trip communicates with Archer and T'Pol as he works on the Xyrillian engine. ,

  4. May 3, 2010  11:15 pm by Mark Manning Reply

    Actually, this is a terrible way to do the SHOW TABLES/COLUMNS/WHATEVER commands. Here is a great example: ME! :-)



    Where I work, I have no privileges on the database other than on a test database where I do my work. Even on there though I have limited privileges. Throw into this conundrum the fact that I'm trying to document the layout of all of the tables (something that has never been done here) and you suddenly get disaster. I can not log directly into the databases - I have to go through their command interpreter. Thus, I can not do the dX options at all because I have no command line interface. If Postgresql had the SHOW XXX commands, then I could still get the layout of the tables. Because PSQL doesn't have the SHOW XXX commands - I can't do this. Wish it had it.

  5. November 23, 2010  8:35 am by Felix Egli Reply

    Mark, you can use the SELECT ... form.
    SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';

  6. March 25, 2011  10:59 pm by Nande! Reply

    really awesome, just what i've been looking for, nothing else alike in the web.

  7. May 8, 2011  8:29 pm by Rob Rasner Facebook Reply

    TQ for this info … I currently doing a number of these suggestions but there are many others might be new to us

  8. May 13, 2011  8:46 am by uotos Reply

    A reading system is an ideal tool for teaching your child to read. Essentially, this is a system that has been formulated and used specifically for teaching

  9. June 2, 2011  5:34 am by what is a hemroid Reply

    Excellent blog site, mate! Postgresql: show tables, show databases, show columns - Linux * Screw is basically a thing. I am launching my very own shortly i will certainly backup elements of the one you have, legally needless to say :)

  10. February 3, 2012  6:22 am by Nisar Reply

    Thanks

  11. Pingback : Web Development | Pearltrees

  12. June 21, 2012  12:52 pm by Manish Reply

    Thanks mate.. really appreciated..

  13. August 21, 2012  8:23 am by Aashish Kiran Reply

    Thank you, your blog helped.
    Keeping posting blogs.
    Thanks,
    Aashish

  14. October 12, 2012  8:03 am by NULL Reply

    ', (delete from *)

  15. Pingback : Postgresql: show tables, show databases, show columns, describe table « My private-public notes

  16. November 15, 2012  10:42 pm by Miguel Reply

    en mysql:
    SELECT * FROM information_schema.columns WHERE table_name = 'tableName' AND table_schema = 'bdName';
    en postgresql:
    SELECT * FROM information_schema.columns WHERE table_name = 'tableName' AND table_catalog = 'bdName';

  17. December 7, 2012  8:00 pm by Greg Reply

    thanks , this was of great help

  18. December 10, 2012  1:20 am by Thomas Reply

    Yeah, please Add more !

    Very cool thanks, coming up from MySQL, this tips saved me some time working on production during a demo :D.

    example : \c database to use another database. equivalent to use database; in mysql

  19. Pingback : PostgreSQL for MySQLlers | WebApps.ie Blog

  20. February 15, 2013  8:30 am by lbrcold Reply

    Thanks. It very helpfull.

  21. February 24, 2013  11:48 am by Gerard Wills Reply

    In PostgreSQL we often want to know what the structure of the table is, how many records are in the table and what the table contains. The following psql commands will provide this.

    select column_name, is_nullable, data_type,
    character_maximum_length as max_length, numeric_precision as precision
    from information_schema.columns where table_name = 'applicant';

    select count(*) from applicant;

    select * from applicant order by 1;

Leave a reply

 

Your email address will not be published.