PostgreSQL is a very popular choice among SQL users. It offers a variety of features and is compatible with numerous programming languages. Thousands of programmers use Postgres to store all the data of their web or mobile applications securely. Postgres users can create database objects like tables, views, sequences, etc., and use them whenever needed. However, performing a task on any existing database object must be done appropriately; otherwise, it may lead to inconvenience. Therefore, it's a good practice to describe the database object first, and then perform any particular functionality on them accordingly. Doing so will reduce the chances of human errors or any other inconvenience.
Quick Outline
This Postgres blog will cover the following sections:
- What are Database Objects?
- What are Meta Commands in Postgres?
- How to Describe Database Objects in PostgreSQL Using psql?
- Bonus Tip 1: How to Describe Postgres Schemas Using psql?
- Bonus Tip 2: How to Describe Users Using psql?
- Final Thoughts
Let’s get started.
What are Database Objects?
Any entity/object like a table, view, sequence, etc. that is defined in a database and is used to store or reference data is known as a database object. The most popularly used database object is a table that keeps the data in a well-structured manner. Other objects include views, sequences, indexes, tablespaces, functions, etc. In PostgreSQL, the database objects are created using the CREATE command.
What are Meta Commands in PostgreSQL?
Meta commands can be used to achieve different functionalities, such as describing any particular database object. These commands are executed only in the SQL Shell (psql). Meta commands are short commands that are executed using a backslash “\”. These commands start with a “\” symbol followed by the command verb and then the desired argument.
How to Describe Database Objects in PostgreSQL From psql?
SQL Shell or psql supports several meta-commands that help us perform different database operations, including describing the database objects. In this write-up, we will execute the different meta commands to describe the following database objects:
- Describe All Tables, Sequences, and Views Using \d
- Describe Only Tables Using \dt
- Describe a Single Table Using \d
- Describe Views Using \dv
- Describe Sequences Using \ds
- Describe Functions Using \df
Let’s implement each of the above-mentioned meta-commands practically.
Example 1: Describe All Tables, Sequences, and Views Using \d
Relations/Tables are among the most important objects of any database that stores the data in a well-organized manner. Database users often describe the relations before performing any particular functionality on them to avoid any inconvenience. This can be done using the “\d” meta-command.
The “\d” is one of the most popularly utilized commands that retrieve all the relations, including tables, sequences, views, and materialized views. To implement this command, first log into the psql, write “\d”, and hit the “ENTER” key. As a result, you will get all the details regarding all available relations in the selected database, as shown in the following snippet:
\d
Executing the above-stated meta-command will retrieve the name of the schema in which the relation is located, the name of the relation, its type, and its owner.
Execute the stated command with the “+” symbol to describe the relations with more details like “persistence”, “access method”, “size”, and “description”:
\d+
Do not use a semicolon at the end of the “\d” command; otherwise, you will encounter an “invalid command” error.
Example 2: Describe Only Tables Using \dt
Do you wanna get a list of all tables only, excluding views and sequences? Don't worry! This can be done by executing the “\dt” meta-command. This psql command will retrieve/describe all the tables with the following information: schema name, table name, type, and respective owner. The “\dt” command can be executed with or without a “;”. Here is a practical example that demonstrates this command better:
\dt
Specify a “+” symbol at the end of the “\dt” command to describe the tables with some additional information like “table size”, “access method”, etc.
\dt+
Example 3: Describe a Single Table Using \d
The “\d” command can be executed with the table name to describe a particular table. For this purpose, the user must use the “\d” command followed by the table name:
\d author_info
On successful execution, the stated command will retrieve the following information: the table columns, data types of the columns, nullable attribute, and default value, as shown in the following screenshot:
Example 4: Describe Views Using \dv
In databases, views are virtual tables that are used to represent the result set of single or multiple tables. SQL Shell supports a very convenient meta-command named “\dv” that is used to describe Postgres views. This command retrieves information about Postgres views including the schema name to which the view belongs, the name of the view, its type, and the owner of the view.
\dv
Run the “\dv” command along with the “+” sign to fetch the list of views with additional details:
\dv+
Example 5: Describe Sequences Using \ds
Sequences are database objects that are used to create/generate a series of integers according to the desired specifications. Oftentimes users come across a situation where they want to check the sequence structure before performing any particular operation on any existing sequence. In such cases, users can describe the selected sequence.
To describe PostgreSQL sequences, execute the “\ds” with the following syntax:
\ds
The “\ds” command retrieves all available sequences from the current database, which can be verified from the “Type” column.
Execute the “\ds” command with the “+” symbol to get the sequences’ list with more details like size, description, and persistence:
\ds+
Example 6: Describe Functions Using \df
Functions are reusable code blocks that reduce the developers' efforts, enhance code efficiency, reduce code redundancy, and save a lot of time. Postgres allows users to create new functions according to their needs, which can be accessed and re-used whenever needed. However, it's a good practice to describe the already-created functions before accessing or using them. To do that, the “\df” meta-command can be executed from psql:
\df
The stated meta-command will retrieve details about all available functions and stored procedures:
Use the “+” symbol with the “\df” command to get more details about the available functions, such as owner, security, language, etc.,
\df+
That’s all about describing a database object in PostgreSQL using psql’s meta-commands.
Bonus Tip 1: How to Describe Postgres Schemas Using psql?
A schema in Postgres is like a container that allows us to organize the database objects into logical groups. Postgres allows us to create several schemas in a single database. Each schema can have its own set of objects like tables, views, etc.
The need to describe a schema arises when we have to check the schema’s owner, its access privileges, or its description. In such cases, several commands and queries can be executed. Among them, the most convenient one is the “\dn+” meta-command
\dn+
Bonus Tip 2: How to Describe Users Using psql?
In database management systems like Postgres, roles, and users are created to handle access control. A user can be a normal user or a superuser and each user has different attributes and access privileges. Therefore, describing a user before accessing it minimizes the chances of errors and provides a detailed understanding of the users. For this purpose, execute the following meta-command:
\du
The above-provided meta-command will retrieve a list of roles along with their attributes:
To learn more about describing users or schemas, navigate to the following dedicated guides: Show Users in Postgres, List Schemas in Postgres.
Final Thoughts
In Postgres, different meta-commands are used to describe database objects using psql. For instance, use the “\d”, “\dt”, “\dv”, “\ds”, and “\df” commands to describe relations, tables, views, sequences, and functions, respectively. All these meta-commands can be executed with the “+” symbol to get more details about any of these objects. All these meta-commands are implemented practically in this guide to give you a profound knowledge of describing database objects in Postgres using psql.
