Postgresql: show tables, show databases, show columns, describe table

  • Databases
  • 2 min read

postgresql logo

PostgreSQL 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?
  • How can I get the list of databases in Postgres like “show databases” in MySQL?

In this quick tutorial, we’ll answer these questions, along with some other common commands which are useful.

PostgreSQL doesn’t work with original MySQL commands, but it gives similar functionality with its own commands:

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

postgresql: \l
postgresql: SELECT datname FROM pg_database;

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

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

If there are any other equivalent commands you’d like to learn for Postgres which we’ve missed above, feel free to let us know in the comments section and we’ll get them added.


27 thoughts on “Postgresql: show tables, show databases, show columns, describe table”

  1. 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.

  2. Rob Rasner Facebook

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

  3. 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

  4. what is a hemroid

    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 🙂

  5. Pingback: Web Development | Pearltrees

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

  7. 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’;

  8. 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

  9. Pingback: PostgreSQL for MySQLlers | Blog

  10. 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;

  11. Pingback: PostgreSQL basic command | imron02

  12. nice Newport Beach massage therapy

    I got this website from my pal who informed me on the topic of this website and now this time I
    am visiting this site and reading very informative articles or reviews

  13. Good day! Do you know if they make any plugins to protect against hackers?

    I’m kinda paranoid about losing everything I’ve worked hard
    on. Any tips?

  14. all the time i used to read smaller content that also clear their motive, and that is
    also happening with this article which I am reading at this place.

Leave a Reply

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