If this PostgreSQL Tutorial saves you hours of work, please whitelist it in your ad blocker 😭 and

Donate Now

to help us вќ¤пёЏ pay for the web hosting fee and CDN to keep the website running.

Summary: in this tutorial, you will learn how to show tables in PostgreSQL using psql tool and pg_catalog schema.

If you are coming from MySQL, you may want to use the popular SHOW TABLES statement that displays all tables in a specific database.

PostgreSQL does not support the SHOW TABLES statement directly but provides you with an alternative.

First, connect to PostgreSQL using psql tool.

.wp-block-code {

      border: 0;
      padding: 0;

}

.wp-block-code > div {

      overflow: auto;

}

.shcb-language {

      border: 0;
      clip: rect(1px, 1px, 1px, 1px);
      -webkit-clip-path: inset(50%);
      clip-path: inset(50%);
      height: 1px;
      margin: -1px;
      overflow: hidden;
      padding: 0;
      position: absolute;
      width: 1px;
      word-wrap: normal;
      word-break: normal;

}

.hljs {

      box-sizing: border-box;

}

.hljs.shcb-code-table {

      display: table;
      width: 100%;

}

.hljs.shcb-code-table > .shcb-loc {

      color: inherit;
      display: table-row;
      width: 100%;

}

.hljs.shcb-code-table .shcb-loc > span {

      display: table-cell;

}

.wp-block-code code.hljs:not(.shcb-wrap-lines) {

      white-space: pre;

}

.wp-block-code code.hljs.shcb-wrap-lines {

      white-space: pre-wrap;

}

.hljs.shcb-line-numbers {

      border-spacing: 0;
      counter-reset: line;

}

.hljs.shcb-line-numbers > .shcb-loc {

      counter-increment: line;

}

.hljs.shcb-line-numbers .shcb-loc > span {

      padding-left: 0.75em;

}

.hljs.shcb-line-numbers .shcb-loc::before {

      border-right: 1px solid #ddd;
      content: counter(line);
      display: table-cell;
      padding: 0 0.75em;
      text-align: right;
      -webkit-user-select: none;
      -moz-user-select: none;
      -ms-user-select: none;
      user-select: none;
      white-space: nowrap;
      width: 1%;

}

$ psql -U postgres -W

The -U flag stands for the user and -W option requires you to provide the password. In this command, you use the postgres user to log in to the PostgreSQL database server.

Secodn, enter the password for the user postgres and press the Enter keywboard:

Password for user postgres: postgres=#

Code language: PHP (php)

Third, switch to a database e.g.., dvdrental:

postgres=# \c dvdrental You are now connected to database «dvdrental» as user «postgres».

Code language: PHP (php)

Note that you can connect to a specific database when you log in to the PostgreSQL database server:

$ psql -U postgres -d dvdrental

In this command, -d flag means database. In this command, you connect to the dvdrental datbase using the postgres user.

Third, use the \dt command from the PostgreSQL command prompt to show tables in the dvdrental database:

postgres=# \dt

Code language: PHP (php)

Output:

To get more information on tables, you can use the \dt+ command. It will add the size and description columns:

postgres=# \dt+

Code language: PHP (php)

Another way to show tables in PostgreSQL is to use the SELECT statement to query data from the PostgreSQL catalog as follows:

SELECT * FROM pg_catalog.pg_tables WHERE schemaname != 'pg_catalog' AND schemaname != 'information_schema';

Code language: SQL (Structured Query Language) (sql)

In this query, we used a condition in the WHERE clause to filter system tables. If you omit the WHERE clause, you will get many tables including the system tables.

• Use the \dt or \dt+ command in psql to show tables in a specific database.
• Use the SELECT statement to query table information from the pg_catalog.pg_tables catalog.

Was this tutorial helpful ?

Yes No

window.googletag = window.googletag || {cmd: []}; googletag.cmd.push(function() {

  googletag.defineSlot%%('/%%21849154601,22306944310/Ad.Plus-336x280', %%[[%%336, 280], [200, 200], [320, 100], [240, 133], [300, 100], [120, 240], [180, 150], [320, 250], [250, 250], [300, 250], [300, 75]], 'Ad.Plus-336x280-2').addService(googletag.pubads())
  .setTargeting%%('%%site', %%['%%postgresqltutorial.com']);
  googletag.enableServices(); googletag.display%%('%%Ad.Plus-336x280-2'); });