PostgreSQL Command-Line Interface (CLI) Tutorial

Introduction to PostgreSQL CLI

PostgreSQL comes with a powerful command-line interface (CLI) called psql, which allows you to interact with PostgreSQL databases directly from the terminal or command prompt.

Connecting to a PostgreSQL Database

To connect to a PostgreSQL database using psql, use the following command:

psql -U username -d database_name -h hostname -p port

Replace username, database_name, hostname, and port with your actual database credentials.

psql -U myuser -d mydatabase -h localhost -p 5432

Basic PostgreSQL Commands

Once connected, you can execute SQL commands directly in the psql prompt:

SELECT * FROM table_name;

SELECT * FROM users;

You can also execute meta-commands prefixed with \ in psql to perform administrative tasks:

\l    -- List all databases
\dt   -- List all tables in the current database
\q    -- Quit the psql session

Executing SQL Scripts

You can execute SQL scripts from files using psql:

psql -U username -d database_name -h hostname -p port -f path_to_script.sql

psql -U myuser -d mydatabase -h localhost -p 5432 -f script.sql

Advanced PostgreSQL CLI Usage

For more advanced usage, psql offers features like:

• Transaction management
• User and role management
• Table creation and modification
• Index management
• And more...

Conclusion

The PostgreSQL CLI (psql) is a powerful tool for managing PostgreSQL databases from the command line. It provides both interactive and scriptable interfaces, making it essential for database administrators and developers alike.