2 Ways to View the Structure of a Table in PostgreSQL

This concise article walks you through a couple of different ways to show the structure (schema) of a table in a PostgreSQL database. Without any further ado, let’s see the things that matter.

Using psql

1. Open a command line window, log yourself into your PostgreSQL cluster, then connect to the database you want to use. I have a database called kindacode and I will select it like so:

\c kindacode

Screenshot:

2. Now you can inspect the structure of a table by making use of one of the following commands:

• \d [table name]: Return basic information about a table
• \d+ [table name]: Returns verbose information about a table

In my case, I have a table called books in the kindacode database. The screenshot below depicts the result I got after running \d books:

And here’s what I got with \d+ books:

Don’t forget to use your own database and table names.

Executing an SQL query

You can run the following query to retrieve the information on columns of a table:

SELECT 
   table_name, 
   column_name, 
   data_type 
FROM 
   information_schema.columns
WHERE 
   table_name = 'your table name';

Example:

Using pgAdmin gives me a more friendly interface:

You can find more details about the information schema in the official Postgres manual.

Conclusion

You’ve learned more than one technique to describe a table in your Postgres database. If you’d like to explore more stuff like this, take a look at the following articles:

• Unsigned (non-negative) Integer in PostgreSQL
• 2 Ways to Rename a Table in PostgreSQL
• Using ENUM Type in TypeORM
• TypeORM: Property ‘id’ has no initializer and is not definitely assigned in the constructor
• MongoDB: Renaming a Database with Command Line
• SQLite: Select Random Records from a Table

You can also check out our database topic page for the latest tutorials and examples.