PostgreSQL Tutorial

PostgreSQL (often called Postgres) is an advanced, enterprise-class **Open Source Relational Database Management System (RDBMS)**. It supports both SQL (relational) and JSON (non-relational) querying, making it one of the most stable and compliant databases available today.

1. PostgreSQL Architecture

PostgreSQL uses a client-server model. A single server process manages the database files, accepts connections from client applications, and performs actions on behalf of the clients.

2. Basic SQL Commands

Here are the fundamental commands you need to get started with Postgres:

Create a Table

CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    email TEXT UNIQUE,
    enrollment_date DATE DEFAULT CURRENT_DATE
);

Insert Data

INSERT INTO students (name, email) 
VALUES ('John Doe', 'john@example.com');

Select Data

SELECT * FROM students WHERE name = 'John Doe';

3. Why use PostgreSQL?

Feature Description
ACID CompliantEnsures data integrity and reliable transactions.
ExtensibilitySupports custom data types, functions, and plugins.
JSON SupportAllows you to store and query NoSQL data easily.
ConcurrencyUses MVCC (Multi-Version Concurrency Control) to allow multiple users to work at once.

Knowledge Check

1. PostgreSQL is primarily what type of database?
A) NoSQL only | B) Relational (RDBMS) | C) Key-Value Store

2. Which feature allows Postgres to handle multiple users without locking the database?
A) MVCC | B) Binary Logging | C) Table Locking

3. Can PostgreSQL store JSON data?
A) No | B) Yes