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:
SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';
SELECT datname FROM pg_database;
postgresql: SELECT column_name FROM information_schema.columns WHERE table_name =’table’;
SELECT column_name FROM information_schema.columns WHERE table_name ='
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.
11 thoughts on “Postgresql: show tables, show databases, show columns, describe table”
COOL! please add more!
Mark, you can use the SELECT … form.
SELECT table_name FROM information_schema.tables WHERE table_schema = ‘public’;
Thanks mate.. really appreciated..
Thank you, your blog helped.
Keeping posting blogs.
‘, (delete from *)
SELECT * FROM information_schema.columns WHERE table_name = ‘tableName’ AND table_schema = ‘bdName’;
SELECT * FROM information_schema.columns WHERE table_name = ‘tableName’ AND table_catalog = ‘bdName’;
thanks , this was of great help
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
Thanks. It very helpfull.
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;