ClassDB Home | Table of Contents

---

Listing and Describing Tables

Author: Steven Rollo

A common activity for both students and instructors is listing all tables in a schema, and describing individual columns in a table. This document will explain how to use the ClassDB catalog management functions for this purpose. These functions provide a simple way to perform these operations, and are intended to be used by students. This document will also demonstrate other ways to perform these operations, such as querying the INFORMATION_SCHEMA.

Listing Tables

This section will show four different methods to list all tables in a given schema.

ClassDB public.listTables()

ClassDB provides the public.listTables() function as an easy way to list all tables in a single schema. Since public is on all ClassDB users’ search paths by default, public can be omitted from the function call. The following query displays a list of all tables in the current user’s $user schema:

SELECT * FROM listTables();

Optionally, a schema name may be provided to the function. The following query lists all tables in the schema public:

SELECT * FROM listTables('public');

Note that users must have at least USAGE privileges on the target schema, and SELECT on each contained object to use listTables. Lacking USAGE privileges on a target schema will cause no objects to be listed, while lacking SELECT privileges on a contained object will cause that specific object to not be listed. Internally, both instances of the function execute a query against the INFORMATION_SCHEMA.

INFORMATION_SCHEMA.TABLES

Querying the INFORMATION_SCHEMA.TABLES view can also provide a list of all tables in a schema. This may be used on its own, or combined with other INFORMATION_SCHEMA queries. It may be helpful to show more advanced students the following query similar to the one used by the public.listTables() function:

SELECT table_schema, table_name, table_type
FROM INFORMATION_SCHEMA.TABLES i JOIN foldedpgSchema fs ON
i.table_schema  = '<schema_name>';

pg_tables

Postgres also provides a system view called pg_tables, which contains a list of all tables in the database. The following query can be used against it, similar to the INFORMATION_SCHEMA query:

SELECT *
FROM pg_tables
WHERE schemaname = '<schema_name>';

psql \dt Command

The psql command line client also contains a helper command \dt, which lists all tables matching a certain pattern. Executing \dt by itself lists all tables you are the owner of. Optionally, \dt takes a string parameter consisting of a pattern to match against table names. For example, the following command lists all tables in the public schema:

\dt public.*

Describing Tables

Describing a table refers to listing all columns in a table. The four methods above can also be applied to describing table.

ClassDB public.describe() Function

ClassDB provides the public.describe() function to list all columns in a given table. It takes up to two parameters, one table name, and an optional schema name. If no schema name is given, the user’s current schema is assumed. Note that describne() has the same access restrictions as listTables(). The following two queries demonstrate the usage of describe(). The first describes the table mytable in the current schema, while the second describes the table shelter.dog.

SELECT * FROM describe('mytable');

SELECT * FROM describe('shelter', 'dog');

INFORMATION_SCHEMA.COLUMNS

The INFORMATION_SCHEMA.COLUMNS view maintains a list of all columns in the DBMS. The following query lists all the columns in the shelter.dog table.

SELECT table_name, column_name, data_type, character_maximum_length
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'dog'
AND table_schema = 'shelter';

psql \d Command

The psql \d command describes one table. This command provides much more information than the previous two queries, although it is limited to use in the psql client.

---