PostgreSQL Notes

Introduction

What is a Database?

An organized collection of data.

It provides a way to store, access, and manipulate data efficiently.

What is a DBMS?

Database Management System (DBMS) is software used to create, store, retrieve, and manage data.

What is an RDBMS?

Relational Database Management System (RDBMS) stores data in structured tables (rows and columns) and uses SQL for querying and managing data.

What is SQL?

Structured Query Language (SQL) is used to communicate with databases.

SQL Shell Commands

List Existing Databases

\list

Clear Screen

\! cls

Create a Database

CREATE DATABASE TestingDB;

Database Management

Connect to PostgreSQL from CMD

psql -U postgres -h localhost

List Existing Databases

SELECT datname FROM pg_database;

Or

\l

Change Database

\c testingdb

Delete a Database

DROP DATABASE db_name;

CRUD Operations

CRUD stands for:

• CREATE
• READ
• UPDATE
• DELETE

Working with Tables

What is a Table?

A table is a collection of related data held in a tabular format within a database.

Creating a Table

CREATE TABLE person (
    id INT,
    name VARCHAR(100),
    city VARCHAR(100)
);

View Table Structure

\d person

Adding Data into a Table

INSERT INTO person(id, name, city)
VALUES (101, 'Atonu', 'Jashore');

Or

INSERT INTO person
VALUES (102, 'Likhon', 'Jashore');

Reading Data from a Table

Show All Columns

SELECT * FROM person;

Show Specific Columns

SELECT name FROM person;

Updating Data in a Table

UPDATE person
SET city = 'Dhaka'
WHERE id = 2;

Deleting Data from a Table

DELETE FROM person
WHERE name = 'Atonu';