An easy way to describe table in psql

One of the core tasks in PostgreSQL is to examine the structure of the table. We frequently look at the structure of the table to find out more about the following subjects:.

1. Column Names
2. Data Types
3. Is this column nullable?
4. Column’s default value
5. Table indexes
6. Table triggers

PostgreSQL provides two ways to check the structure of the table.

1. \d command
2. information schema tables

Checking table structure using \d command in PostgreSQL

Psql in PostgreSQL has a built-in command to examine the table metadata, similar to the MySQL DESCRIBE TABLE command. Use the “\d <table_name>” command in PostgreSQL to view the table structure. All of the aforementioned data is displayed on the screen.

Describe Table Syntax

The syntax of the describe table in PostgreSQL is shown below:

\d <table_name>

Put the target table name in place of <table_name>.

Describe Table Example

In this example, I have created an employee table. We can check the structure of this table using \d command as shown below.

\d employee

In the above screenshot, \d command provides the below information.

1. Column Names
2. Data Type
3. Collation
4. Nullable
5. Default Value
6. Indexes
7. Foreign Key
8. Triggers

Column Name	Data Type	Nullable	Default	Indexes	Foreign Key
employee_id	integer	not null	Auto Increment	Primary key
name	varchar(100)	not null		Unique
salary	numeric	nullable	5000
department_id	integer	nullable			Yes

Checking table structure using information schema tables

We can also use the information_schema.columns view to check the table structure. The columns view provides many useful details in addition to column names and data types, which are not available in the “\d” command in PostgreSQL.

select * from information_schema.columns where table_name='employee'

The information_schema.columns tables provide the following information:.

1. table catalog
2. table schema
3. table name
4. column name
5. ordinal position
6. column default value
7. is nullable
8. data type
9. character datatype max length
10. numeric datatype precision
11. datetime precision

Check Table Constraints in PostgreSQL

To check table constraints, we can use the information_schema.table_constraints table. Below is an example.

select * from information_schema.table_constraints where table_name='employee'

This table provides following information.

1. Constraint name
2. Constraint schema
3. Table name
4. Table schema
5. Constraint type (Primary Key, Foreign Key, Unique)

Identify all columns in the table that are used by some constraint, and then we can use the information_schema.constraint_column_usage view.

select * from information_schema.constraint_column_usage where table_name='employee'

Check sequences in the database

To check all sequences (auto-increment) in a database, we can use the information_schema.sequences view. This view also provides sequence start value, increment number, and maximum value allowed for this sequence.

select * from information_schema.sequences

Check all tables and view in database

To check all tables and view in current database, we can use the information_schema.table view.

select * from information_schema.tables

In the above screenshot, it is showing two tables department, and employee and it also showing one view high_salary_employees.

Triggers on a table

To check all triggers on a table, we can use the information_schema.triggers table. This table provides the trigger name, table name, action condition, action statement, action timing (BEFORE/AFTER).

select * from information_schema.triggers where event_object_table='employee'

Check all views on table

To check all views, use the information_schema.views view. This view provides table name and show all the text defined in the view.

select * from information_schema.views where table_name='high_salary_employees'

Summary

In PostgreSQL, examine the table structure is essential for day to day work. PostgreSQL provides two ways to describe the table structure \d command and information schema tables. \d is a quick way to check table structure, but if want to find additional details you can use information schema tables.