Skip to main content
← Back to blog
Share on
How to list tables in PostgreSQL
3 min read

Build the internal tools you need(Dashboard, Workflow Management, CRM, Support and Billing management...) in minutes.

How to list tables in PostgreSQL

This article introduces two methods to list tables in a PostgreSQL database. PostgreSQL provides two ways to list all tables in a database:

  • Use \dt or \dt+ in the psql tool to list all tables in the current database.
  • Query all tables from the pg_tables table.

List Tables in the Database Using \dt​

This example demonstrates the process of logging into the database using the psql tool and listing tables in the database. Follow these steps:

  1. Log in to the PostgreSQL server using the postgres user:
[~] psql -U postgres
psql (14.4)
Type "help" for help.

Note: You can also use any user with the appropriate database permissions.

  1. Select the testdb database with the following statement:
\c testdb;

If the database hasn't been created yet, run the following statement:

CREATE DATABASE testdb;
  1. Use the \dt command to list all tables in the testdb database:
\dt
            List of relations
Schema |      Name      | Type  |  Owner
--------+----------------+-------+----------
public | mytable        | table | postgres
public | product        | table | postgres
public | test_date      | table | postgres
public | test_time      | table | postgres
public | test_timestamp | table | postgres
public | week_day_sales | table | postgres
(6 rows)
  1. If you want to view more information about the tables, use the \dt+ command:
\dt+
                                          List of relations
Schema |      Name      | Type  |  Owner   | Persistence | Access method |    Size    | Description
--------+----------------+-------+----------+-------------+---------------+------------+-------------
public | mytable        | table | postgres | permanent   | heap          | 16 kB      |
public | product        | table | postgres | permanent   | heap          | 16 kB      |
public | test_date      | table | postgres | permanent   | heap          | 8192 bytes |
public | test_time      | table | postgres | permanent   | heap          | 8192 bytes |
public | test_timestamp | table | postgres | permanent   | heap          | 8192 bytes |
public | week_day_sales | table | postgres | permanent   | heap          | 8192 bytes |
(6 rows)

You can see that the input of \dt+ includes columns such as Persistence, Access method, Size, and Description in addition to the output of \dt.

Query Tables from the pg_tables Table​

In addition to the \dt and \dt+ commands, you can also query all tables in the current data from the pg_tables table.

The pg_tables table is a built-in table in PostgreSQL that stores all tables in the database.

SELECT * FROM pg_tables
WHERE schemaname = 'public';
 schemaname |   tablename    | tableowner | tablespace | hasindexes | hasrules | hastriggers | rowsecurity
------------+----------------+------------+------------+------------+----------+-------------+-------------
 public     | test_date      | postgres   |            | t          | f        | f           | f
 public     | test_time      | postgres   |            | t          | f        | f           | f
 public     | test_timestamp | postgres   |            | t          | f        | f           | f
 public     | week_day_sales | postgres   |            | t          | f        | f           | f
 public     | mytable        | postgres   |            | f          | f        | f           | f
 public     | product        | postgres   |            | t          | f        | f           | f
(6 rows)

Conclusion​

PostgreSQL provides two ways to list all tables in a database:

  • Use \dt or \dt+ in the psql tool to list all tables in the current database.
  • Query all tables from the pg_tables table.

In MySQL, you can use the SHOW TABLES command to list databases.

Related Articles

PostgreSQL SELECT Statement

This article introduces the basic form of the `SELECT` statement in `PostgreSQL`, as well as how to use `SELECT` statements to query data from tables. In `PostgreSQL`, the `SELECT` statement is used to retrieve data from one or more tables and it is perhaps the most widely used statement.

Best Practices for PostgreSQL ISNULL and ISNOTNULL

This article explains how to use the `IS NULL` operator in PostgreSQL to check if a value is `NULL`. PostgreSQL `IS NULL` is a boolean operator that checks if a value is `NULL`. A `NULL` value is a special value that indicates nothing, it is neither an empty string nor false.

Using Remark to Create an Interactive Table of Contents in a Next.js Blog

A table of contents has numerous benefits, and is a valuable addition for websites, especially blogs. An organized and easily navigable table of contents significantly improves the user experience, simplifying the process for readers to find information they require. By adding a table of contents, not only do you provide readers with streamlined navigation, but you also increase the overall accessibility and usability of content.