PostgreSQL DESCRIBE TABLE

Introduction to describing tables in PostgreSQL

Describing tables in PostgreSQL provides a comprehensive overview of the database structural elements, which makes data manipulation and retrieval a lot less of a hustle. It is especially useful when dealing with complex databases, as it clearly highlights all the relationships between different tables. In this guide, we will dive into different methods of describing PostgreSQL tables in order to improve your database experience: be it with the help of a good-old command line tool or a new convenient GUI — dbForge Studio for PostgreSQL.

Using the psql command line tool

If you have been working with PostgreSQL for at least some time, you are most probably familiar with SQL Shell (psql). It is a powerful open-source relational database management system which provides a command-line interface to interact with PostgreSQL databases. In this section of our today's guide, we will be looking into using it in terms of our objective: describing tables. We will demonstrate the usage of the basic commands that can be handy for our purposes.

To begin the experiments with the psql commands, let us prepare our playground first:

1. In the Windows search bar, type SQL Shell.

2. Enter the following credentials to connect to the Postgres database:

• Server
• Database
• Port
• Username
• Password

Now, as we are in, we can start working with describing tables.

Basic command (\d)

The \d command is a frequently used meta-command that provides a quick and easy way to list table structures, offering a detailed view of the schema of any table in the database.

As you can see, psql returns the table names, as well as the schema names they belong to, their type, and owner.

Detailed table description (\d table_name)

To get detailed information about a specific table, append the table name to the \d command:

\d table_name

In the output, you will see the structure of the said table, including columns, data types, and constraints.

From the output, we can determine that the customers table is located in the sales schema and find detailed information about its columns, data types, constraints, and relationships with other tables. For example, the customer_id column is referenced by the orders table through the orders_customer_id_fkey foreign key constraint, establishing a connection between customers and their orders.

Querying information schema

In the upcoming section we are going to stay on the same root and keep working with SQL Shell (psql). However, we will use information_schema instead of meta-commands. Below, you will find a brief overview of the subjects and several illustrative examples to help visual learners understand them better.

Overview of information schema

information_schema is a collection of views that provide details about the objects in the current database. It follows the SQL standard, making it more portable and stable compared to PostgreSQL's system catalogs, which are unique to PostgreSQL and based on implementation needs. However, the information schema does not include details about PostgreSQL-specific features. For that information, you need to look at the system catalogs or other PostgreSQL-specific views. Let's see how it works!

Querying table columns

In order to retrieve details about the columns in a specific table, you can query the information_schema.columnsi view. This view contains information about every column in every table in the database. The following query is going to fetch column details for the customers table in the sales schema:

SELECT 
    table_schema,
    table_name,
    column_name,
    data_type,
    character_maximum_length,
    is_nullable,
    column_default
FROM 
    information_schema.columns
WHERE 
    table_schema = 'sales' 
    AND table_name = 'customers';

In the results table, you can find the schema name, table name, column name, data type, maximum character length (for character types), whether the column is nullable, and the default value for each column in the customers table.

Querying table constraints

The information_schema.table_constraints view can be queried to get information about the constraints on a specific table.

SELECT 
    constraint_name,
    constraint_type,
    table_name
FROM 
    information_schema.table_constraints
WHERE 
    table_schema = 'sales' 
    AND table_name = 'customers';

As the name suggests, this view provides information about table constraints such as primary keys, foreign keys, unique constraints, and check constraints:

For a more detailed understanding of the columns involved in each constraint, you can join the information_schema.key_column_usage view:

SELECT 
    tc.constraint_name, 
    tc.constraint_type, 
    kcu.column_name
FROM 
    information_schema.table_constraints AS tc
JOIN 
    information_schema.key_column_usage AS kcu 
    ON tc.constraint_name = kcu.constraint_name
WHERE 
    tc.table_schema = 'sales' 
    AND tc.table_name = 'customers';

This query will provide the constraint name, constraint type, and the columns involved in each constraint for the specified table.

Exploring the pg_catalog schema

Before we shift our attention to the GUI instruments, the last (but certainly not least) command-line tool for describing tables on our agenda today is pg_catalog schema.

Overview of pg_catalog

Each PostgreSQL database contains a pg_catalog schema, which contains the system tables and all the built-in data types, functions, and operators. The pg_tables view within the said schema provides information about all the tables in the database. This view is particularly useful for listing the tables and their attributes, such as schema, table name, owner, and whether the table is a base table or a view.

Using pg_tables

To retrieve a list of tables and their attributes, you can use the following query:

SELECT 
    schemaname,
    tablename,
    tableowner,
    tablespace,
    hasindexes,
    hasrules,
    hastriggers
FROM 
    pg_catalog.pg_tables
WHERE 
    schemaname NOT IN ('pg_catalog', 'information_schema');

This query will return the schema name, table name, table owner, tablespace, and boolean flags indicating if the table has indexes, rules, or triggers for all user-defined tables (excluding system and information schema tables).

Using pg_attribute and pg_constraint

In order to retrieve detailed information about columns and constraints, you can use the pg_attribute and pg_constraint system catalogs. These catalogs provide low-level details about the columns and constraints in your tables.

The pg_attribute catalog contains information about table columns, such as their names, data types, and attributes.

SELECT 
    a.attnum AS column_id,
    a.attname AS column_name,
    pg_catalog.format_type(a.atttypid, a.atttypmod) AS data_type,
    a.attnotnull AS not_null,
    a.attlen AS length,
    a.attndims AS array_dims
FROM 
    pg_catalog.pg_attribute a
JOIN 
    pg_catalog.pg_class c ON a.attrelid = c.oid
JOIN 
    pg_catalog.pg_namespace n ON c.relnamespace = n.oid
WHERE 
    n.nspname = 'sales' 
    AND c.relname = 'customers' 
    AND a.attnum > 0 
    AND NOT a.attisdropped;

This query retrieves the column ID, column name, data type, not null constraint, length, and array dimensions for all columns in the sales.customers table.

The pg_constraint catalog provides information about table constraints, including primary keys, foreign keys, unique constraints, and check constraints.

SELECT 
    conname AS constraint_name,
    pg_catalog.pg_get_constraintdef(c.oid, true) AS constraint_definition
FROM 
    pg_catalog.pg_constraint c
JOIN 
    pg_catalog.pg_class rel ON rel.oid = c.conrelid
JOIN 
    pg_catalog.pg_namespace n ON n.oid = rel.relnamespace
WHERE 
    n.nspname = 'sales' 
    AND rel.relname = 'customers';

This query retrieves the constraint name and its definition for all constraints on the sales.customers table.

Describing tables with dbForge Studio for PostgreSQL

Since we are done reviewing different command-line-based ways to describe tables in PostgreSQL, we can now shift our attention to something more convenient. Namely, we will be using dbForge Studio for PostgreSQL.

Introduction to dbForge Studio

dbForge Studio is a comprehensive graphical tool designed for efficient database management and development. It provides a user-friendly interface that simplifies various database tasks, including designing, editing, and managing PostgreSQL databases. With its rich feature set, the Studio allows you to execute queries, develop procedures, and manage data with ease.

Using the Table Properties window

The Table Properties window in dbForge Studio is a powerful feature that allows users to view and manage table structures effortlessly. By selecting a table and opening its properties, you can access detailed information about the table's columns, indexes, and constraints.

To access the Table Properties window, right-click the desired table in Database Explorer and select Properties.

In the window that opens, you will see all the table properties neatly displayed as a table:

To see information regarding other tables, columns, constraints, and any other database object, simply select it in the Database Explorer, and it will be displayed accordingly in the Table Properties window.

Generating SQL scripts

With dbForge Studio for PostgreSQL, you can generate DDL scripts for database objects using the Generate Script AS CREATE option.

The tool allows you to script the following source objects:

• Views
• Materialized Views
• Functions
• Trigger Functions
• Procedures
• Triggers

To generate a script for the CREATE statement:

1. Right-click the database object you wish to generate a script for.

2. Point to Generate Script AS and select Create.

3. Select the output format for the script:

• To New SQL Window,
• To File,
• To Clipboard.

Here, we selected the first option and have the generated script in the new SQL window.

Generating scripts for database objects can be very useful if you need to monitor database changes. You can always restore an old database and compare objects, but having the objects scripted would be easier to access the code. The feature also comes in handy when you need to deploy just a few changed objects from one database to another.

Conclusion

Understanding the structure of tables in PostgreSQL is essential for effective database management. Both the command-line interface and graphical tools like dbForge Studio for PostgreSQL offer robust solutions for describing table structures. While the command-line interface provides a powerful and flexible way to retrieve detailed information, the intuitive features of dbForge Studio are especially beneficial for beginners. Ultimately, dbForge Studio can significantly enhance productivity and efficiency in managing PostgreSQL databases.