PostgreSQL Getting Started

In this lesson you install PostgreSQL, open the psql client and create the institute database.

Install PostgreSQL

System	Easiest way
Windows	Download the installer from the official PostgreSQL website. It includes the server, `psql` and pgAdmin.
macOS	Install Postgres.app, or run `brew install postgresql@17`
Ubuntu Linux	`sudo apt install postgresql`

The installer creates a main admin user called postgres. On Windows and macOS you choose its password during setup.

No time to install? Free cloud services such as Supabase and Neon give you a real PostgreSQL database in the browser, with a SQL editor. Everything in this tutorial works there.

Connect with psql

psql is the command-line client that comes with PostgreSQL.

Example

bash

psql -U postgres

On Ubuntu, the usual way is:

Example

bash

sudo -u postgres psql

After logging in, you see a prompt like postgres=#. The word before =# is the current database. Check your version:

Example

sql

SELECT version();

Output

                          version
-----------------------------------------------------------
 PostgreSQL 17.6 on x86_64-pc-linux-gnu, compiled by gcc...
(1 row)

Your exact text will be different.

Create a Database

Example

sql

CREATE DATABASE institute;

Output

CREATE DATABASE

psql answers each command with a short word that tells you what was done. You can also create a database from the terminal, without opening psql: createdb -U postgres institute.

Meta-Commands

Commands that start with a backslash are psql meta-commands. They are not SQL; psql handles them itself, and they do not need a semicolon.

Command	What it does
`\l`	List all databases
`\c institute`	Connect to the database `institute`
`\dt`	List tables in the current database
`\d students`	Describe the columns of a table
`\dn`	List schemas
`\du`	List users (roles)
`\i file.sql`	Run the SQL in a file
`\x`	Switch to expanded output for wide rows
`\?`	Help for meta-commands
`\q`	Quit psql

Switch to the new database:

Example

text

postgres=# \c institute
You are now connected to database "institute" as user "postgres".
institute=#

The prompt now shows institute=#.

Schemas

Inside a database, tables live in a schema, a kind of folder. Every new database has a schema called public, and tables go there unless you say otherwise. So students and public.students mean the same table.

Example

sql

CREATE SCHEMA reports;
CREATE TABLE reports.monthly_summary (month DATE, total INT);

Most small projects use only public.

Users and Roles

It is good practice not to let your app log in as postgres. Create a separate user for it:

Example

sql

CREATE ROLE webapp WITH LOGIN PASSWORD 'change-me';
GRANT CONNECT ON DATABASE institute TO webapp;

You would then give it rights on the tables it needs with GRANT SELECT, INSERT, UPDATE, DELETE ON ... TO webapp;.

Delete a Database

Example

sql

DROP DATABASE IF EXISTS old_test;

You cannot drop the database you are connected to. Connect to another one (such as postgres) first.

Graphical Tools

pgAdmin: the official free admin tool
DBeaver and TablePlus: desktop tools that work with many databases
The SQL editor in Supabase or Neon, in the browser

Exercise

Which psql command lists the tables in the current database?

\l\dtSHOW TABLES;\q