Skip to content

Section 3 — Data Types & Constraints.md

Latest commit

 

History

History
343 lines (243 loc) · 9.22 KB

Section 3 — Data Types & Constraints.md

File metadata and controls

343 lines (243 loc) · 9.22 KB
Raw

⚙️ Section 3 — Data Types & Constraints

(Back to Table of Contents)

📘 3.1 What are Data Types?

🧠 Concept

A data type specifies the kind of data that can be stored in a column. This helps PostgreSQL understand how to store, compare, and calculate with that data.

Category Examples Description
Numeric INT, FLOAT, DECIMAL, DOUBLE Stores numbers
String/Text VARCHAR, CHAR, TEXT Stores characters or words
Date/Time DATE, TIME, TIMESTAMP Stores temporal data
Boolean BOOLEAN True/False values
Serial SERIAL Auto-incremented integer

📗 3.2 Numeric Data Types

Type Description Example
INT Integer number 10
FLOAT Floating number (approximate) 10.5
DECIMAL(p, s) Fixed precision number — p = total digits, s = digits after decimal DECIMAL(5,2) → allows 999.99

🧩 Example:

CREATE TABLE prices (
    item_id INT,
    cost DECIMAL(5,2)
);

INSERT INTO prices VALUES
(1, 99.99),
(2, 10.75),
(3, 5.50);

Result Table:

item_id cost
1 99.99
2 10.75
3 5.50

🧵 3.3 String Data Types

Type Description Example
VARCHAR(n) Variable length (up to n chars) VARCHAR(50)
CHAR(n) Fixed length (pads spaces) CHAR(10)
TEXT Unlimited text Paragraphs

🧩 Example:

CREATE TABLE users (
    id INT,
    name VARCHAR(50),
    bio TEXT
);

INSERT INTO users VALUES
(1, 'Amit', 'PostgreSQL learner and database enthusiast.');

Result Table:

id name bio
1 Amit PostgreSQL learner and database enthusiast.

📅 3.4 Date and Boolean Data Types

Type Example Output
DATE '2025-10-14' 2025-10-14
TIME '13:45:00' 13:45:00
TIMESTAMP '2025-10-14 13:45:00' 2025-10-14 13:45:00
BOOLEAN TRUE or FALSE TRUE

🧩 Example:

CREATE TABLE meetings (
    meeting_id INT,
    meeting_date DATE,
    start_time TIME,
    is_online BOOLEAN
);

INSERT INTO meetings VALUES
(1, '2025-10-14', '10:00:00', TRUE),
(2, '2025-10-15', '15:30:00', FALSE);

Result Table:

meeting_id meeting_date start_time is_online
1 2025-10-14 10:00:00 TRUE
2 2025-10-15 15:30:00 FALSE

🧩 3.5 Constraints in PostgreSQL

🧠 Concept

A constraint is a rule applied to a column to control the kind of data that can be stored.

They ensure data integrity — no invalid, duplicate, or missing data sneaks into your tables.

Constraint Purpose Example
PRIMARY KEY Uniquely identifies each record PRIMARY KEY (id)
NOT NULL Column cannot be empty name VARCHAR(50) NOT NULL
UNIQUE Prevents duplicate values email UNIQUE
DEFAULT Assigns default value if none given DEFAULT 'India'
CHECK Validates condition CHECK (salary > 0)
FOREIGN KEY Links to another table REFERENCES departments(id)

🧱 3.6 PRIMARY KEY

The PRIMARY KEY uniquely identifies each record and cannot contain NULL.

🧩 Example:

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    name VARCHAR(50),
    dept VARCHAR(50)
);

INSERT INTO employees VALUES
(1, 'Raj', 'IT'),
(2, 'Priya', 'HR');

If you try to insert a duplicate:

INSERT INTO employees VALUES (1, 'Amit', 'Finance');

❌ Error: duplicate key value violates unique constraint "employees_pkey"

Table Result:

emp_id name dept
1 Raj IT
2 Priya HR

🚫 3.7 NOT NULL Constraint

Prevents a column from having NULL (empty) values.

🧩 Example:

CREATE TABLE customers (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100)
);

INSERT INTO customers (id, email) VALUES (1, 'abc@example.com');

❌ Error: null value in column "name" violates not-null constraint

✅ Correct:

INSERT INTO customers VALUES (1, 'Raju', 'abc@example.com');
id name email
1 Raju abc@example.com

🧩 3.8 DEFAULT Value

Assigns a default value automatically if no data is given.

CREATE TABLE employees_default (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50),
    country VARCHAR(50) DEFAULT 'India'
);

INSERT INTO employees_default (name) VALUES ('Suman');

Result Table:

id name country
1 Suman India

🔢 3.9 SERIAL (Auto Increment)

SERIAL auto-generates integer IDs sequentially.

🧩 Example:

CREATE TABLE departments (
    dept_id SERIAL PRIMARY KEY,
    dept_name VARCHAR(50)
);

INSERT INTO departments (dept_name) VALUES ('IT'), ('Finance'), ('HR');

Result Table:

dept_id dept_name
1 IT
2 Finance
3 HR

🧩 3.10 UNIQUE Constraint

Ensures all values in a column are distinct.

CREATE TABLE accounts (
    user_id SERIAL PRIMARY KEY,
    email VARCHAR(100) UNIQUE
);

INSERT INTO accounts (email) VALUES
('a@example.com'),
('b@example.com'),
('a@example.com');  -- ❌ Duplicate

❌ Error: duplicate key value violates unique constraint

✅ Correct Result:

user_id email
1 a@example.com
2 b@example.com

🧪 3.11 CHECK Constraint

Validates values before insertion.

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50),
    price DECIMAL(10,2) CHECK (price > 0)
);

INSERT INTO products (name, price) VALUES ('Pen', -5.00);  -- ❌ Error
INSERT INTO products (name, price) VALUES ('Book', 50.00); -- ✅ Works

Result Table:

id name price
1 Book 50.00

🔧 3.12 Combined Example

Let’s create a full table with multiple constraints.

CREATE TABLE employees_full (
    emp_id SERIAL PRIMARY KEY,
    fname VARCHAR(50) NOT NULL,
    lname VARCHAR(50),
    email VARCHAR(100) UNIQUE,
    dept VARCHAR(50) DEFAULT 'General',
    salary NUMERIC CHECK (salary > 0)
);

✅ Insert Example:

INSERT INTO employees_full (fname, lname, email, salary)
VALUES ('Ravi', 'Sharma', 'ravi@company.com', 55000);

Result Table:

emp_id fname lname email dept salary
1 Ravi Sharma ravi@company.com General 55000

🧠 Section 3 — Summary

Concept Example
Data Types INT, VARCHAR, DATE, BOOLEAN
Primary Key PRIMARY KEY (id)
Not Null name VARCHAR(50) NOT NULL
Unique email UNIQUE
Default country VARCHAR(50) DEFAULT 'India'
Check CHECK (salary > 0)
Serial SERIAL PRIMARY KEY

✅ Next: Section 4 — Clauses and Operators →