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.
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:
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';
Using pgAdmin gives me a more friendly interface:
You can find more details about the information schema in the official Postgres manual.
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.