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

PostgreSQL

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.

PSQL Tool in PgAdmin

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

List of Databases

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

List of Database Tables with Details

List of database tables with size and description.

\dt+

Details List of Database Table

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>

Details of a Database Table

List of Database Users

To show all users and their scopes.

\du

List of Database Users

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>

Details of a Database User

List of Database Schemas

To get all database schemas name and basic informations.

\dn

List of Database Schemas

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

Save Database Results in CSV

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

Save Database Results in Text Formatted File

Export Limited Results in File

To save query result with limited rows value.

Save Query Results in File

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