Home » Programming » Databases » Postgresql Show Tables Show Databases Show Columns

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

by

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:

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';

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.

SHARE:
Related Articles
Photo of author
Author
Stefan Durand
My name is Stefan, I'm the admin of LinuxScrew. I am a full-time Linux/Unix sysadmin, a hobby Python programmer, and a part-time blogger. I post useful guides, tips, and tutorials on common Linux and Programming issues. Feel free to reach out in the comment section.

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

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

    Reply

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

    Reply

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

    Reply

Leave a Comment