PgAdmin tutorial to work with Tables, Columns, Constraints, Indexes, RLS Policy, Rules, and Triggers by creating and managing an Employees table.

---

1. Creating the Employees Table

Before working with columns, constraints, and indexes, we need to create the Employees table.

Steps in PgAdmin:

1. Open PgAdmin and connect to your database.

2. In the Object Browser, right-click Tables under your schema (e.g., public).

3. Click Create → Table.

4. In the General tab, enter:

   • Name: Employees

   • Schema: public

5. Click Save.

---

2. Adding Columns to Employees Table

Steps in PgAdmin:

1. In the Object Browser, expand Tables → Employees.

2. Right-click Columns and select New Column.

3. Fill in the column details:

   • Name: employee_id

   • Data Type: SERIAL

   • Primary Key: Yes

4. Add additional columns:

   • first_name (VARCHAR(50), Not Null)

   • last_name (VARCHAR(50), Not Null)

   • email (VARCHAR(100), Unique, Not Null)

   • phone_number (VARCHAR(15), Nullable)

   • hire_date (DATE, Not Null)

   • job_id (VARCHAR(10), Not Null)

   • salary (DECIMAL(10,2), Not Null)

5. Click Save.

---

3. Adding Constraints to Employees Table

Types of Constraints:

• Primary Key: Ensures unique and non-null employee_id.

• Unique Constraint: Ensures no duplicate emails.

• Check Constraint: Ensures salary is positive.

• Foreign Key Constraint: Links job_id to Jobs table.

Steps to Add Constraints:

1. In PgAdmin, expand Tables → Employees → Constraints.

2. Right-click Constraints → New Constraint.

3. Primary Key:

   • Name: pk_employee

   • Type: Primary Key

   • Columns: employee_id

   • Save.

4. Unique Constraint:

   • Name: unique_email

   • Type: Unique

   • Columns: email

   • Save.

5. Check Constraint (Salary > 0):

   • Name: check_salary

   • Type: Check

   • Expression: salary > 0

   • Save.

6. Foreign Key Constraint:

   • Name: fk_job

   • Type: Foreign Key

   • Column: job_id

   • References Table: Jobs

   • References Column: job_id

   • On Delete: CASCADE

   • Save.

---

4. Creating Indexes on Employees Table

Indexes improve query performance.

Steps to Create an Index:

1. Expand Tables → Employees → Indexes.

2. Right-click Indexes → New Index.

3. General Tab:

   • Name: idx_last_name

   • Type: B-tree

4. Columns Tab:

   • Select Column: last_name

   • Sort Order: Ascending

   • Save.

---

5. Setting Up Row-Level Security (RLS) Policy

Row-Level Security (RLS) controls access to table rows.

Steps to Enable RLS:

1. Expand Tables → Employees → RLS Policy.

2. Right-click RLS Policy → New RLS Policy.

3. General Tab:

   • Name: policy_employee

   • Command: SELECT

4. Using Expression: current_user = email

5. Save.

This ensures that employees can only view their own records.

---

6. Creating Rules for Automatic Data Modification

Rules allow automatic updates or inserts.

Steps to Create a Rule:

1. Expand Tables → Employees → Rules.

2. Right-click Rules → New Rule.

3. General Tab:

   • Name: rule_no_delete

   • Event: DELETE

   • Condition: true

   • Instead: DO NOTHING

4. Save.

This prevents deletion of records.

---

7. Creating Triggers in Employees Table

Triggers execute a function when an event occurs.

Steps to Create a Trigger:

1. Expand Tables → Employees → Triggers.

2. Right-click Triggers → New Trigger.

3. General Tab:

   • Name: before_insert_employee

   • Events: BEFORE INSERT

   • Trigger Function: audit_employee()

4. Save.

Steps to Create a Trigger Function:

1. Open Query Tool.

2. Run the SQL function:

    sqlCopyEditCREATE OR REPLACE FUNCTION audit_employee()
    RETURNS TRIGGER AS $$
    BEGIN
        INSERT INTO employee_audit (employee_id, action, timestamp)
        VALUES (NEW.employee_id, 'INSERT', NOW());
        RETURN NEW;
    END;
    $$ LANGUAGE plpgsql;
   

3. Click Execute.

---

Final Notes

• Use Query Tool (Ctrl + E) to test SQL queries.

• Use SELECT * FROM employees; to verify changes.

• Backup data before applying constraints or triggers.

This tutorial covers creating tables, adding constraints, defining indexes, implementing row-level security, creating rules, and using triggers in PgAdmin.