Postgres supports numerous commands to perform various database operations. To execute such commands different interfaces are used. One such interface is “SQL Shell”, also known as, “psql”. Using psql, you can execute various commands to accomplish different database operations efficiently, such as accessing, creating, deleting, or updating a database, table, schema, etc.
Try the latest PgManage (Open Source) and get rid of PgAdmin!
This post presents a comprehensive understanding of basic “psql” commands through practical demonstration.
Contact us today for all your Postgres and Open Source consulting and support needs.
Introduction to Basic SQL Shell or psql Commands
The psql commands assist us in querying the data from the specified database interactively. Here are some of the most frequently used, most effective psql commands:
- Connect to a Database: “psql -d db_name -U user_name”.
- Check Postgres Version: “SELECT VERSION();”.
- List All Databases: “\l”.
- Access or Switch a Database: “\c db_name”.
- List All Tables: “\dt”.
- Describe All Tables: “\d”.
- Describe a Specific Table: “\d tab_name”.
- List All Schemas: “\dn”.
- List All Views: “\dv”.
- List All Functions: “\df”.
- List All Users: “\du”.
- Show Commands History: “\s”
- Save Query’s Results to a Specific File: “\o file_name”.
- Run psql Commands/queries From a Particular File: “\i file_name”.
- Execute Previous Command: “\g”.
- Show Query Execution Time: “\timing”.
- Get Output in HTML Format: “\H”.
- Align Columns Output: “\a”.
- Get Help: “\h”.
- Get All psql Commands: “\?”.
- Clear Screen: “\! cls”.
- Quit psql: “\q”.
Let’s put these commands into practice to get a profound understanding.
Example 1: Connecting to a Database
Open the CMD and execute the below-provided psql command to establish a connection to a particular database:
psql -d postgres -U postgres
The output signifies that a connection with the “postgres” database has been successfully established under the user “postgres”.
Example 2: Checking Postgres Version
Executing the “SELECT VERSION();” command will retrieve the currently installed Postgres version:
SELECT VERSION();
The output shows that currently “PostgreSQL 15.1” is running on our system.
Example 3: Listing All Databases
Listing available databases is a very common task in Postgres that can be accomplished via the “\l” command:
\l
The “\l” successfully retrieves the list of available databases.
Example 4: Accessing/Switching a Database
Performing any operation on a database object requires accessing that database. To accomplish this task, execute the “\c” command from the “SQL Shell”:
\c sample_db;
The connection with the “sample_db” database has been established successfully.
Example 5: Listing All Available Tables
In Postgres, the tables are used to represent the data elements in a well-organized format. Run the “\dt” command from SQL Shell to fetch the list of available tables/relations:
\dt;
The stated command returns all the tables available in the selected database.
Example 6: Describing All Tables
Postgres users can use the “\d” command to get the list of relations, including sequences, views, etc.
\d
The “\d” command successfully retrieves the “schema name”, “table name”, “relation type”, and owner.
Example 7: Describing a Specific Table
Execute the “\d” command followed by the table name to describe a specific table in Postgres:
\d emp_data;
The above-stated command will describe the “emp_data” table:
The stated command retrieves all the details regarding the “emp_data” table, such as column names, column types, columns’ default values, etc.
Example 8: Listing All Schemas
A Postgres schema is a namespace that keeps the database objects, such as relations, functions, etc. To fetch the list of schemas, use the “\dn” command:
\dn;
The given command returns the names of all schemas along with their owners.
Example 9: Listing All Views
Views are a frequently utilized concept in Postgres that allows us to simplify complex queries. To show the list of views, use the “\dv” command:
\dv;
The “\dv” command returns the view name, schema name, relation type, and view’s owner.
Example 10: Listing All Functions
In Postgres, the functions enhance code reusability, understandability, debugging, etc. To obtain the list of available functions, use the “\df”:
\df;
The “\df” command returns the “schema name”, “function name”, “result data types”, and “argument data types”.
Example 11: Listing All Users
In PostgreSQL, the users can have database privileges and can own the database objects, such as tables, schemas, etc. To get the user’s list, use the below command:
\du;
The “\du” command returns the “role name”, “attributes”, and “members details”.
Example 12: Show Command History
Open the terminal, log into “psql”, and execute the following command to see the query history:
\s
The output shows that the “\s” command successfully retrieves the query history.
Example 13: Execute psql Commands From a Particular File
The SQL Shell supports a “\o” command that allows us to save query results to a specific file. Execute the “\o” command followed by the “file name”, as shown below:
\o 'C:/exeFile.txt';
The cursor moves to the next line, which proves that the “\o” command executes successfully. Now the output of the commands will be written to the “exeFile.txt” file until we execute the “\o” command:
Let’s open the desired file to see the result of the “\dt” command:
The output snippet verified the working of the “\o” command.
Example 14: Execute psql Commands From a Particular File
Postgres allows us to execute psql commands from a particular file using the “\i”. The sample file contains the following command:
Let’s utilize the “\i” command to execute the psql commands from the selected file:
\i 'C:/showtables.txt';
The output proves that the “\i” command successfully executes the commands from a specific file.
Example 15: Show Query Execution Time
In psql, the “\timing” command is used to enable or disable query execution time:
\timing
Let’s execute any command to see how the “\timing” command works:
SELECT * FROM emp_data;
Execute the “\timing” command one more time to disable the query execution time:
\timing
The output shows that the query execution has been “off”.
Example 16: Get Output in HTML Format
The “\H” command is used in psql to get the command’s output in HTML format:
\H
Now the output of any particular command will be displayed in HTML format as follows:
SELECT * FROM emp_data;
To disable the HTML format, use the “\H” command one more time.
Example 17: Execute Previous Command
Use the “\g” command to run the previously executed command:
\g
The “\g” command retrieves the result based on the previously executed command.
Example 18: Align Columns Output
Execute the “\a” command to align or unaligned the output format:
\a
The output snippet shows that the output format is “unaligned”. Run any Postgres-supported command to understand this concept better:
SELECT * FROM emp_data;
The output proves that the result set is unaligned.
Example 19: Get Help
Use the “\h” command to get help regarding any command or query. For instance, the below command will provide help regarding the “INSERT INTO” command:
\h INSERT INTO
The “\h” command retrieves the details regarding the “INSERT” command.
Example 20: Get All psql Commands
Execute the “\?” command to get all available psql commands:
\?
The output displays all available commands in psql.
Example 21: Clear Screen
Execute the “\! cls” command from psql to clear the screen:
\! cls
Hitting the enter button will clear the screen.
Example 22: Quit psql
The “\q” command is used to quit or exit the SQL Shell (psql):
\q
That’s all! We have discussed various basic yet very important psql commands.
Conclusion
psql is a command-line interface used to perform various database tasks efficiently. For instance, using psql different commands can be executed to access, create, delete, or update a database, table, schema, etc. Moreover, using psql, you can store the output of commands to a specific file or execute the commands from a particular file. In this write-up, we have discussed the most frequently used psql commands with practical demonstration.
