Standardized Approaches to Exploring Database Structure in PostgreSQL: From MySQL's SHOW TABLES and DESCRIBE to information_schema Views

Introduction: Transition Challenges from MySQL to PostgreSQL

Many developers transitioning from MySQL to PostgreSQL initially encounter a common issue: the familiar SHOW TABLES and DESCRIBE table commands are not directly available in PostgreSQL. These commands are actually MySQL-specific administrative commands, not part of standard SQL. In PostgreSQL, database structure exploration requires different mechanisms, reflecting the design philosophy differences between the two database systems.

PostgreSQL's Structure Exploration Mechanisms

PostgreSQL provides multiple ways to explore database structure, with the most SQL-standard compliant approach being the use of information_schema views. These views are part of the SQL standard and are implemented in most modern database systems, including MySQL, PostgreSQL, and Microsoft SQL Server. Unlike MySQL-specific commands, information_schema offers cross-database compatibility, making code more portable.

Standardized Query Alternative to SHOW TABLES

To obtain a list of tables in the current database, the following standardized SQL query can be used:

SELECT
    table_schema || '.' || table_name as show_tables
FROM
    information_schema.tables
WHERE
    table_type = 'BASE TABLE'
AND
    table_schema NOT IN ('pg_catalog', 'information_schema');

This query retrieves all base tables from the information_schema.tables view while excluding tables from system catalogs and the information_schema itself. The table_schema || '.' || table_name portion concatenates the schema name and table name, providing complete table identifiers.

Creating Reusable Table List Functions

To enhance query convenience and reusability, an SQL function can be created:

create or replace function show_tables() returns SETOF text as $$
SELECT
    table_schema || '.' || table_name as show_tables
FROM
    information_schema.tables
WHERE
    table_type = 'BASE TABLE'
AND
    table_schema NOT IN ('pg_catalog', 'information_schema');
$$
language sql;

After creating this function, table lists can be obtained through a simple select show_tables() call, significantly simplifying daily database exploration tasks.

Alternative to DESCRIBE TABLE for Structure Queries

To obtain structure information for a specific table, the following query can be used:

select column_name, data_type, character_maximum_length
from INFORMATION_SCHEMA.COLUMNS where table_name ='table_name';

This query retrieves all column information for the specified table from the information_schema.columns view, including column names, data types, and character maximum lengths (where applicable).

Creating Table Structure Description Functions

Similarly, a function can be created to encapsulate table structure queries:

create or replace function describe_table(tbl_name text) returns table(column_name   
varchar, data_type varchar,character_maximum_length int) as $$
select column_name, data_type, character_maximum_length
from INFORMATION_SCHEMA.COLUMNS where table_name = $1;
$$
language 'sql';

When using this function, simply execute select * from describe_table('a_table_name'); to obtain complete structure information for the specified table.

Comparison with Other Methods

In addition to the information_schema approach, PostgreSQL offers other ways to explore database structure. As mentioned in Answer 1, the psql command-line tool provides \d and \d+ tablename commands, but these are client-tool specific features that cannot be used in standard SQL queries. Graphical management tools like PgAdmin-III also offer structure browsing capabilities, but these similarly are not standard SQL-based solutions.

Practical Recommendations and Best Practices

In actual development, it is recommended to choose appropriate methods based on specific scenarios: for immediate interactive exploration, psql's \d commands may be more convenient; for scenarios requiring dynamic database structure retrieval in application code, information_schema queries provide standardized solutions; and for situations requiring frequent execution of the same structure queries, creating custom functions can improve code maintainability and reusability.

Conclusion

PostgreSQL provides standardized, cross-database compatible database structure exploration mechanisms through information_schema views. Although different in syntax from MySQL's SHOW TABLES and DESCRIBE commands, by understanding and utilizing information_schema, developers can gain more powerful and flexible structure query capabilities. The methods introduced in this paper not only address specific issues when migrating from MySQL to PostgreSQL but also provide systematic solutions for effective database exploration in PostgreSQL environments.