- 20 Jul, 2024
- 0 Comments
- 4 Mins Read
PostgreSQL: Basic psql Commands
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.
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”.
Open the CMD and execute the below-provided psql command to establish a connection to a particular database:
Check Postgres Version: “SELECT VERSION();”.
Executing the “SELECT VERSION();” command will retrieve the currently installed Postgres version:
SELECT VERSION();
List All Databases: “\l”.
Listing available databases is a very common task in Postgres that can be accomplished via the “\l” command:
\l
Access or Switch a Database: “\c db_name”
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
List All Tables: “\dt”.
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;
Describe All Tables: “\d”.
Postgres users can use the “\d” command to get the list of relations, including sequences, views, etc.
\d
Describe a Specific Table: “\d tab_name”
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,
List All Schemas: “\dn”.
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.
List All Views: “\dv”.
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.
List All Functions: “\df”.
In Postgres, the functions enhance code reusability, understandability, debugging, etc. To obtain the list of available functions, use the “\df”:
\df;
List All Users: “\du”.
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;
Show Commands History: “\s”
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.
Save Query’s Results to a Specific File: “\o file_name”.
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:
Run psql Commands/queries From a Particular File: “\i file_name”.
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.
Show Query Execution Time: “\timing”.
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”.
Get Output in HTML Format: “\H”.
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.
Execute Previous Command: “\g”.
Use the “\g” command to run the previously executed command:
\g
The “\g” command retrieves the result based on the previously executed command.
Align Columns Output: “\a”.
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.
Get Help: “\h”.
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.
Get All psql Commands: “\?”.
Execute the “\?” command to get all available psql commands:
\?
The output displays all available commands in psql.
Clear Screen: “\! cls”.
Execute the “\! cls” command from psql to clear the screen:
\! cls
Hitting the enter button will clear the screen.
Quit psql: “\q”.
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.