PostgreSQL’s Command-Line (PSQL) Tool and Commands

What is PostgreSQL Command-Line Tool (PSQL)?
PostgreSQL is an open-source relational database management system (RDBMS) known for its extensibility and robust features. PSQL is a command-line tool that comes bundled with the PostgreSQL installation. It provides a command-line interface for executing SQL queries, managing databases, and performing various administrative tasks. It is not a standalone tool but rather a client application used to interact with a PostgreSQL database server.

Why do we use PSQL Tool?
The psql tool, which is the command-line interface for PostgreSQL, is a powerful and versatile tool that offers several advantages, making it a preferred choice for interacting with PostgreSQL databases.
Interactive SQL Console: PSQL tool provides an interactive console where you can execute SQL queries and commands directly. This interactive mode allows for quick and direct interaction with the database, making it easy to test queries and explore data.
Command-Line Operations: We can perform various database operations directly from the command line, such as creating databases, tables, and users, as well as managing permissions and roles.
Connection to Remote Servers: PSQL tool allows us to connect to PostgreSQL database servers, even if they are on remote machines. This is valuable for managing and querying databases on servers that are not local.
Database Administration: It offers various administrative features, allowing us to perform tasks like creating and restoring database backups, managing user roles, and monitoring database performance.
Security: PSQL tool supports secure connections to PostgreSQL servers, ensuring that data transmission is encrypted. This is important for maintaining the security of sensitive information.
Extensibility and Customization: We can customize the PSQL environment by configuring settings, defining aliases, and creating custom scripts. This allows users to tailor the tool to their specific needs and preferences.
What are the commands for PSQL?
Connect Database
The initial phase of data operations is database connection. In this case, we have database in our own host (e.g. localhost) or in other host. We can connect both type of database through PSQL commands.
I. Database in same host
psql -d <db-name> -U <username> -W
or,
psql -d <db-name> -U <username> -w
Here, -d
flag specifies to database name; -U
flag specifies to user name; -W
specifies to enter user password before connecting to the database; -w
specifies that user password is optional.
Example: Here, my database name is superset_db and username is akash.
psql -d superset_db -U akash –W
II. Remote Database (Host is not same)
psql -h <db-address> -U <username> -p <port-number> -d <db-name> -W
Here, -h
flag specifies the host address of the database; -p
flag specifies the port number of the database.
Example: Here, my database name is remote_db, username is akash, host address is 192.168.10.0 and port 5432 (default).
psql -U akash -h 192.168.10.0 -p 5432 -d remote_db -W
We can ignore port number if it becomes default (5432) otherwise we have to specify it.
List of Databases
With this command we can see all databases as a list.
\l

Switch to another Database
We may need to change database to make operations.
\c <db-name>
List of Database Tables
This command will show all tables as a list.
\dt

List of Database Tables with Details
List of database tables with size and description.
\dt+

Describe a Table
If we want to know details of a specific table. It shows columns, type, collation, nullable status and default value.
\d <table_name>
Describe a table with Details
It shows extra information about a specific DB table.
\d+ <table_name>

List of Database Users
To show all users and their scopes.
\du

Details of a Database User
It shows the details information about a specific user. We can also use \du
where we won’t able to get description of the user.
\du+ <user_name>

List of Database Schemas
To get all database schemas name and basic informations.
\dn

Export Database Results in CSV
To save a specific table information in a CSV file.
\copy table_name(select column_name) To 'full_file_path.csv' WITH CSV HEADER

Export Database Results in Text Reader Format
To save the results of database operation with various kind of text reader like excel, documents.
\copy table_name(select column_name) To 'full_file_path.xlsx' WITH CSV HEADER
or,
\copy table_name(select column_name) To 'full_file_path.txt' WITH CSV HEADER
or,
\copy table_name(select column_name) To 'full_file_path.odt' WITH CSV HEADER
or,
\copy table_name(select column_name) To 'full_file_path.docx' WITH CSV HEADER

Export Limited Results in File
To save query result with limited rows value.

Run Commands from a File
To run multiple commands stored in a file.
\i <file_name>
List of Database Views
To show all database views (virtual tables, joined tables etc.) as a list.
\dv
List of all Database Functions
We can list all the functions from our database with the \df
command.
\df
PSQL Help Commands
To get all commands and hints of PSQL.
\h
Quit PSQL Command
To exit and clear commands.
\q