Skip to content

Listing and Describing Tables

Jump to bottom
Steven Rollo edited this page Jul 4, 2017 · 13 revisions

Listing and Describing Tables

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 meta functions for this purpose. These functions provide a simple way to perform these operations, and are intended to be used by student. 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. The query:

SELECT * FROM public.listTables();

will display a list of all tables in the user's current schema. Optionally, a schema name may be provided to the function, for example:

SELECT * FROM public.listTables('public');

This will list all tables in the schema public instead of the current schema. Internally, this function executes 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. The following query is used by the public.listTables() function:

SELECT table_name, table_type
FROM INFORMATION_SCHEMA.TABLES
WHERE table_schema = '<schema_name>';

where <schema_name> can be any schema. Of course, this function may be used on its own, or combined with other INFORMATION_SCHEMA queries. It may be helpful to show more advanced students this query when they are ready to move beyond using public.listTables().

pg_tables

Postgres also provides a system view called pg_tables, which contains a list of all tables in the DBMS. 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,

\dt classdb.*

will list all tables in the classdb schema.

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. For example:

SELECT * FROM public.Describe('mytable');

describes the table mytable in the current schema, while:

SELECT * FROM public.Describe('dog', 'public');

describes the table public.dog.

INFORMATION_SCHEMA.COLUMNS

The INFORMATION_SCHEMA.COLUMNS view maintains a list of all columns in the DBMS. The query:

SELECT table_name, column_name, data_type, character_maximum_length
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = '<table_name>'
AND table_schema = '<schema_name>';

lists all the columns in some table, in some schema.

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.

Listing-and-Describing-Tables.md - ClassDB Documentation

Steven Rollo
Data Science & Systems Lab (DASSL), Western Connecticut State University (WCSU)

(C) 2017- DASSL. ALL RIGHTS RESERVED.
Licensed to others under CC 4.0 BY-SA-NC: https://creativecommons.org/licenses/by-nc-sa/4.0/

PROVIDED AS IS. NO WARRANTIES EXPRESSED OR IMPLIED. USE AT YOUR OWN RISK.

Clone this wiki locally