PostgreSQL is a open source relational database management system that allows users to store, manage and retrieve structured data.
There are many reasons why PostgreSQL is a top choice for both beginners and professionals when working with relational databases. Below are some of the key reasons:
PostgreSQL is fully ACID compliant, ensuring reliable and safe data operations through Atomicity, Consistency, Isolation, and Durability.
PostgreSQL is highly extensible—you can add extensions like pg_vectorize to run AI models or pgmq to use it as a message queue system.
PostgreSQL supports object-oriented features like custom data types, inheritance, and function overloading, allowing advanced logic within the database.
PostgreSQL is a powerful, flexible, and future-ready database system suitable for both beginners and professionals building real-world applications.
In PostgreSQL, a schema is a namespace that contains tables, views, functions, and other database objects. It's like a container that helps organize and separate your data structures.
Schemas help organize database objects for easier maintenance and logical grouping within a single PostgreSQL database.
PostgreSQL supports multiple schemas, allowing the same table name (e.g., X1) to exist in different schemas (e.g., schema_1 and schema_2), making access and separation convenient.
Having data in separate schemas simplifies the process of backing up and restoring specific parts of the database.
Created by default, the public schema stores objects when no schema is specified and is accessible to all users by default.
Custom schemas are user-defined and provide restricted access, meaning only authorized users can access the objects inside.
The most important elements in a relational database are keys. They maintain the relationship between tables and help uniquely identify data within a table. In a relational database, the two main types of keys are the Primary Key and the Foreign Key.
A Primary key is used to ensure that data in the specific column in unique. It ensures that row is unique, key cannot be null. It is specifically generated by the database according to sequence
A Foreign key is a column or group of columns that creates a link between two tables in relational database. It is a column that references a column of another table
The class_id in Classes is the primary key—a unique ID for each class.
The class_id in Students is the foreign key—it links each student to the class they belong to.
In relational database, there has many data types. To store string data, the most commonly used VARCHAR and CHAR data type.
VARCHAR stands for variable-length character. It stores strings with flexible length up to a defined limit. This helps save space when storing shorter text compared to the maximum allowed length.
CHAR is a fixed-length character data type. It always uses the defined length, padding with spaces if needed, making it ideal for uniform-length data.
The WHERE clause is mostly used clause in postgreSQL. It is used to specify a condition while fetching or modifying data in a database. It allows to filtering data based in queries. WHERE clause filter the rows that are affected by the SELECT, UPDATE AND DELETE OR INSERT operations.
Example:
Imagine you have a table of students with three columns: st_id, st_name, and subject. Now you want to see all students whose subject is "Science".
Syntax:
SELECT * FROM students WHERE subject = 'Science';