PostgreSQL Get Started

Getting Started with PostgreSQL

After installing PostgreSQL, it's time to connect. We will learn how to use the psql command-line tool, which is the interactive terminal for PostgreSQL.

The postgres User

During installation, a superuser is created, typically named postgres. This user has full administrative rights over the database server. On Linux, a system user named postgres is also created.

Connecting with psql

To connect to the database, you use psql. On Linux, you first switch to the postgres user.

sudo -i -u postgres

Then, you can run psql to connect.

psql

On macOS (with Homebrew) or Windows, you can just run psql. You will be greeted with a prompt like postgres=#, which means you are connected to the postgres database as the postgres user.

Basic psql Commands

psql has many internal commands, called "meta-commands," which start with a backslash \.

List Databases

To see all databases on the server, use \l.

\l

Connect to a Database

To switch to a different database, use \c.

\c my_database

List Tables

To see all tables in the current database, use \dt.

\dt

Describe a Table

To see the columns of a table, use \d.

\d my_table

Quit psql

To exit the psql terminal, use \q.

\q

Creating a Database

You can create a new database using a SQL command. SQL commands end with a semicolon ;.

CREATE DATABASE my_new_db;

You can then connect to your new database with \c my_new_db.

Creating a User (Role)

In PostgreSQL, users are called "roles," and a role can have login privileges. Let's create a new role.

CREATE ROLE my_user WITH LOGIN PASSWORD 'my_password';

This creates a user named my_user. They can log in with the specified password.

Granting Privileges

By default, a new user has no permissions, so you need to grant them privileges on your database.

GRANT ALL PRIVILEGES ON DATABASE my_new_db TO my_user;

This gives my_user full control over my_new_db.

Connecting as a New User

You can connect to a specific database as a specific user. Use the -d flag for the database and -U for the user.

psql -d my_new_db -U my_user

You will be prompted to enter the password for my_user.

Summary

psql is your main tool for interacting with PostgreSQL. Meta-commands (\l, \c, \dt) help you navigate, and SQL commands (CREATE, GRANT) manage your database objects.