CRUD operations (Create, Read, Update, Delete) are fundamental when working with PostgreSQL databases. Whether you’re a beginner or an expert, understanding these operations is crucial.
Create Table
Use the CREATE TABLE statement to define a new table:
CREATE TABLE employees (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
salary NUMERIC(10,2),
department VARCHAR(50)
);
PostgreSQL Data Types
PostgreSQL provides a variety of data types, including:
✔️ Character types: char, varchar, text
✔️ Numeric types: integer, numeric, real
✔️ Boolean: true / false
✔️ Temporal types: date, time, timestamp
✔️ JSON: Stores JSON data
✔️ Arrays, Network Address, Geometric Data
Constraints in PostgreSQL
Constraints enforce rules on the data. Key constraints include:
NOT NULL: Ensures a column cannot have a NULL value.
UNIQUE: Ensures all values in a column are different.
PRIMARY KEY: Uniquely identifies each row.
FOREIGN KEY: Constrains data based on columns in other tables.
CHECK: Ensures column values meet certain conditions.
PostgreSQL Functions & Operators
PostgreSQL includes powerful built-in functions and operators:
📌 Aggregate Functions: AVG(), COUNT(), MAX(), MIN(), SUM()
📌 String Functions: CONCAT(), LOWER(), UPPER(), SUBSTRING()
📌 Date & Time Functions: AGE(), NOW(), CURRENT_DATE
📌 Comparison Operators: <, >, <=, >=, !=
📌 Mathematical Operators: +, -, *, /, MOD()
Views, Sequences, and Indexes
✔️ View: CREATE VIEW view_name AS SELECT …;
✔️ Sequence: CREATE SEQUENCE serial START number;
✔️ Index: CREATE INDEX index_name ON table(column_name);
Types of Indexes in PostgreSQL:
🔹 B-Tree (Default): Best for equality and range queries.
🔹 Hash Indexes: Optimized for equality comparisons.
🔹 GIN Indexes: Useful for full-text search and arrays.
🔹 GiST Indexes: Suitable for geometric data types.
🔹 BRIN Indexes: Efficient for very large tables with sorted data.
📌 Mastering these PostgreSQL fundamentals can significantly improve your database performance and efficiency! 🚀