Getting Started with PostgreSQL

In this article, I will cover the first steps needed to use a database.
First, we need to install PostgreSQL. In my case, I am using PostgreSQL on Windows via WSL, so I used the terminal to run the necessary commands.

INSTALLING POSTGRESQL

Before installing any packages, let's update the system:

sudo apt update && sudo apt upgrade -y

Now, let's install PostgreSQL along with additional packages:

sudo apt install postgresql postgresql-contrib -y

Start and enable postgres:

sudo systemctl start postgresql
sudo systemctl enable postgresql

Check if the service is running:

sudo systemctl status postgresql

If everything is working correctly, you should see something like this.

● postgresql.service - PostgreSQL database server
     Loaded: loaded (/usr/lib/systemd/system/postgresql.service; enabled; preset: disabled)
     Active: active (running) since Sun 2025-03-09 08:11:05 -03; 2h 13min ago
 Invocation: 438028c9f2dd4a159e35421d9c15d7fe
       Docs: man:postgres(1)
   Main PID: 747 (postgres)

CREATING A ROLE FOR YOUR USER

Check the name of your user:

whoami

Access the database and create a role for your user.

su postgres
psql -U postgres

The prompt will change to postgres=#, indicating that we are inside the database.
Inside postgres shell run:

CREATE ROLE <user-name> WITH LOGIN SUPERUSER CREATEDB CREATEROLE;

Quit out of postgres:

\q

or

exit

Now run:

psql

CREATING A DATABASE

Run the following command to create a new PostgreSQL database:

CREATE DATABASE database_name;

Now you should be able to connect to postgres just using:

psql

CONNECTING TO THE DATABASE

There are two ways to connect.

By running the following command:

\c database_name

If you are unsure of the database name you want to access, you can list all databases in PostgreSQL by running:

\l

If no database is specified, PostgreSQL will try to connect to the default postgres database created during installation.

CREATING TABLES

To illustrate, let's create a users table:

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150) UNIQUE NOT NULL,
    age INT CHECK (age >= 0),
    create_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

The difference between using NOT NULL and UNIQUE NOT NULL is as follows:
NOT NULL ensures that a column cannot have null values but allows duplicates.
UNIQUE NOT NULL prevents null values and also enforces uniqueness.

CRUD OPERATIONS

CRUD stands for Create, Read, Update, and Delete. The four fundamental operations in database management.

INSERTING VALUES (Create)
When a table is first created, it is empty. To insert values into a PostgreSQL table, use the INSERT INTO command:

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

This command adds rows to the table.

READING DATA (Read)
To do it use the SELECT command:

SELECT name, email FROM users;

This will display rows from the selected columns.

UPDATING DATA (Update)
The UPDATE operation is used when we need to modify existing data in a table, whether to correct a record or update some information. Use the the following command:

UPDATE users
SET email = '[e-mail]'
WHERE name = 'Carlos Silva';

This will update the specified columns for all rows that match the condition.

DELETING DATA (Delete)
The DELETE operation removes data from a table. Use the DELETE command as follows:

DELETE FROM users
WHERE name = 'Carlos Silva';

DELETING A DATABASE

If you want to delete everything and restart for practice, run the command:

DROP DATABASE users;

This will delete the database you created.

Now that we have configured PostgreSQL and explored the basic operations for inserting, querying, updating, and deleting data, you can start experimenting. Practice and explore to get comfortable with data manipulation and database operations. Have fun!