Learn how to use PostgreSQL’s psql command-line tool to manage databases, execute queries, and perform administrative tasks directly from your terminal. This tutorial covers essential commands with practical examples for beginners.

Introduction to PostgreSQL Command Line

PostgreSQL, often referred to as Postgres, is a powerful open-source relational database management system (RDBMS). The command-line interface (CLI) for PostgreSQL provides a robust set of commands for managing databases, users, permissions, and executing SQL queries directly from the terminal.

Prerequisites

Before you begin, ensure that PostgreSQL is installed on your system. You can install it using your system’s package manager or download it from the official PostgreSQL website.

Connecting to PostgreSQL

To connect to PostgreSQL from the command line, use the psql command followed by the necessary connection details (username, database, host, and port).

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

For example:

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

sudo -u postgres psql

Create a database and grant permissions

# Connect to PostgreSQL server as a superuser (e.g., postgres)
sudo -u postgres psql

# Execute the SQL commands
CREATE DATABASE mydatabase;
CREATE USER myuser WITH ENCRYPTED PASSWORD 'mypassword';
GRANT ALL PRIVILEGES ON DATABASE mydatabase TO myuser;

Basic Commands

  1. Listing databases:
   \l

This command lists all databases available on the PostgreSQL server.

  1. Connecting to a database:
   \c database_name

Use this command to connect to a specific database.

  1. Listing tables:
   \dt

Lists all tables in the current database.

  1. Describing a table:
   \d table_name

Displays the structure of a specific table, including columns, types, and constraints.

  1. Executing SQL queries: You can directly execute SQL queries from the command line. For example:
   SELECT * FROM table_name;

Replace table_name with the actual name of the table you want to query.

  1. Exiting PostgreSQL:
   \q

Use this command to exit the PostgreSQL command-line interface.

Examples

Let’s go through some practical examples:

  1. Creating a new database:
   CREATE DATABASE mynewdatabase;
  1. Creating a new table:
   CREATE TABLE users (
       id SERIAL PRIMARY KEY,
       username VARCHAR(50) NOT NULL,
       email VARCHAR(100) NOT NULL UNIQUE
   );
  1. Inserting data into a table:
   INSERT INTO users (username, email) VALUES ('john_doe', 'john@example.com');
  1. Updating data in a table:
   UPDATE users SET email = 'john.doe@example.com' WHERE username = 'john_doe';
  1. Deleting data from a table:
   DELETE FROM users WHERE username = 'john_doe';

Conclusion

The PostgreSQL command-line interface (psql) provides a convenient way to interact with PostgreSQL databases directly from the terminal. It supports a wide range of commands for database management, querying, and data manipulation. Mastering these commands will empower you to efficiently manage and work with PostgreSQL databases in various development and administrative tasks.