Understanding the PostgreSQL

What is the Database?

Database is nothing but the collection of the organised data which can be the actually easy to accessed,retrieved and update.Consider it as the digital filing system where information are kept in organized way .

What is the Database Management System?

A DBMS (Database Management System) is software that is used to create, store, manage, retrieve, and control access to data in a database efficiently and securely.

Types of Database

1. Hierarchical Database

   Data is organized in a tree-like structure (parent–child relationship).

2. Network Database

   Data is stored in a graph structure, allowing many-to-many relationships.

3. Relational Database (RDBMS)

   Data is stored in tables (rows and columns) and managed using SQL.

4. Object-Oriented Database

   Data is stored in the form of objects, similar to OOP concepts.

5. NoSQL Database

   Stores data in formats like documents, key-value pairs, graphs, or columns.

What is PostgreSQL?

PostgreSQL is a powerful, open-source Object Relational Database Management System (ORDBMS) that uses SQL (and more) to store, manage, and retrieve data efficiently.

Is PostgreSQL better than SQL?

This is a very common confusion, so let’s clear it cleanly:

SQL is NOT a database

SQL is a language

• SQL → A language used to talk to databases

• PostgreSQL → A database system that uses SQL

Why PostgreSQL is considered one of the BEST RDBMS

1. Advanced SQL Support

2. ACID Compliance (Very Strong)

3. Performance (Especially for Complex Data)

4. Object-Relational Features

5. JSON + SQL

6. Open Source & Free

PostgreSQL Installation in Linux Ubuntu (terminal)

1. Update The System

sudo apt update

1. Install PostgreSQL

sudo apt install postgresql postgresql-contrib

1. Check status

sudo systemctl status postgresql

1. Switch to postgres user

sudo -i -u postgres

1. Open PostgreSQL shell

psql

You’ll see:

postgres=#

Exit:

\\q

For the PostgreSQL DBeaver are the best software even it more comfortable with the backend as well.

Dbeaver software of PostgreSQL Installation in Linux Ubuntu

1. Download .deb

wget <https://dbeaver.io/files/dbeaver-ce_latest_amd64.deb>

1. Install

sudo apt install ./dbeaver-ce_latest_amd64.deb

Basics of PostgreSQL– CRUD Operations

PostgreSQL is a powerful, open-source relational database management system (RDBMS).

At the core of any database are CRUD operations, which represent the four basic actions performed on data.

CRUD = Create, Read, Update, Delete

Let’s understand each operation with simple examples.

1. CREATE – Inserting Data

The CREATE operation is used to add new data into a table.

Create a Table

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100),
    age INT
);

Insert Data into Table

INSERT INTO users (name, email, age)
VALUES ('Vinit', 'vinit@example.com', 22);

READ – Fetching Data

The READ operation retrieves data from the database.

Fetch All Records

SELECT*FROM users;

Fetch Specific Columns

SELECT name, emailFROM users;

Fetch with Condition

SELECT*FROM usersWHERE age>18;

SELECT is one of the most frequently used commands in PostgreSQL.

3. UPDATE – Modifying Existing Data

The UPDATE operation is used to change existing records.

Update a Record

UPDATE users
SET age=23
WHERE id=1;

Update Multiple Columns

UPDATE users
SET name='Vinit Rokade', email='vinitrokade@example.com'
WHERE id=1;

⚠️ Always use a WHERE clause to avoid updating all records accidentally.

4. DELETE – Removing Data

The DELETE operation removes data from a table.

Delete a Specific Record

DELETEFROM usersWHERE id=1;

Delete All Records

DELETEFROM users;

⚠️ Be careful: deleting without a WHERE clause removes all rows.

CRUD Summary Table

Why CRUD is Important?

• Forms the foundation of database interactions

• Used in backend development, APIs, and applications

• Essential for full-stack development

• Every real-world app (banking, social media, e-commerce) uses CRUD

PostgreSQL Basics – Important Concepts Explained

Understanding PostgreSQL goes beyond CRUD operations. To work efficiently with data, you must know data types, constraints, clauses, operators, and functions. Let’s break them down one by one with simple examples.

1. PostgreSQL Data Types

Data types define what kind of data a column can store.

Common Data Types

Example

CREATE TABLE employees (
    id SERIAL,
    name VARCHAR(100),
    salary NUMERIC,
    is_active BOOLEAN,
    joining_date DATE
);

2. Constraints

Constraints are rules applied to table columns to ensure data integrity.

Data types are a way to limit the kind of data that can be stored in a table. For many applications, however, the constraint they provide is too coarse. For example, a column containing a product price should probably only accept positive values. But there is no standard data type that accepts only positive numbers. Another issue is that you might want to constrain column data with respect to other columns or rows. For example, in a table containing product information, there should be only one row for each product number.

To that end, SQL allows you to define constraints on columns and tables. Constraints give you as much control over the data in your tables as you wish. If a user attempts to store data in a column that would violate a constraint, an error is raised. This applies even if the value came from the default value definition.

Common Constraints

Example

CREATE TABLE users (
    id SERIALPRIMARY KEY,
    emailVARCHAR(100)UNIQUENOT NULL,
    ageINTCHECK (age>=18)
);

1.Check Constraints

A check constraint is the most generic constraint type. It allows you to specify that the value in a certain column must satisfy a Boolean (truth-value) expression. For instance, to require positive product prices, you could use:

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric CHECK (price > 0)
);

As you see, the constraint definition comes after the data type, just like default value definitions. Default values and constraints can be listed in any order. A check constraint consists of the key word CHECK followed by an expression in parentheses. The check constraint expression should involve the column thus constrained, otherwise the constraint would not make too much sense.

You can also give the constraint a separate name. This clarifies error messages and allows you to refer to the constraint when you need to change it. The syntax is:

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric CONSTRAINT positive_price CHECK (price > 0)
);

2.Not-Null Constraints

A not-null constraint simply specifies that a column must not assume the null value. A syntax example:

CREATE TABLE products (
    product_no integer NOT NULL,
    name text NOT NULL,
    price numeric
);

An explicit constraint name can also be specified, for example:

CREATE TABLE products (
    product_no integer NOT NULL,
    name text CONSTRAINT products_name_not_null NOT NULL,
    price numeric
);

A not-null constraint is usually written as a column constraint. The syntax for writing it as a table constraint is

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric,
    NOT NULL product_no,
    NOT NULL name
);

3.Unique Constraints

Unique constraints ensure that the data contained in a column, or a group of columns, is unique among all the rows in the table. The syntax is:

CREATE TABLE products (
    product_no integer UNIQUE,
    name text,
    price numeric
);

when written as a column constraint, and:

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric,
    UNIQUE (product_no)
);

when written as a table constraint.

To define a unique constraint for a group of columns, write it as a table constraint with the column names separated by commas:

CREATE TABLE example (
    a integer,
    b integer,
    c integer,
    UNIQUE (a, c)
);

4.Primary Keys

A primary key constraint indicates that a column, or group of columns, can be used as a unique identifier for rows in the table. This requires that the values be both unique and not null. So, the following two table definitions accept the same data:

CREATE TABLE products (
    product_no integer UNIQUE NOT NULL,
    name text,
    price numeric
);

CREATE TABLE products (
    product_no integer PRIMARY KEY,
    name text,
    price numeric
);

4.Default Constraints

A DEFAULT constraint automatically inserts a value when you don’t provide one during INSERT.

No value given → PostgreSQL uses the default.

Add a DEFAULT value to a column

ALTER TABLE person
ADDCOLUMN statusVARCHAR(20)DEFAULT'active';

Now:

INSERT INTO person (id, mob)
VALUES (102,'9876543210');

status will automatically be 'active'

5.Foreign Keys

A foreign key constraint specifies that the values in a column (or a group of columns) must match the values appearing in some row of another table. We say this maintains the referential integrity between two related tables.

Say you have the product table that we have used several times already:

CREATE TABLE products (
    product_no integer PRIMARY KEY,
    name text,
    price numeric
);

Let's also assume you have a table storing orders of those products. We want to ensure that the orders table only contains orders of products that actually exist. So we define a foreign key constraint in the orders table that references the products table:

CREATE TABLE orders (
    order_id integer PRIMARY KEY,
    product_no integer REFERENCES products (product_no),
    quantity integer
);

3. SQL Clauses

Clauses are used to filter, sort, and limit data in queries.

Common Clauses

• WHERE

• ORDER BY

• GROUP BY

• HAVING

• LIMIT

Example:

SELECT*FROM users
WHERE age>20
ORDERBY ageDESC
LIMIT5;

4. Relational Operators

Relational operators are used to compare values.

Example:

SELECT*FROM usersWHERE age>=18;

5. IN and NOT IN

Used to check if a value matches any value in a list.

IN

SELECT*FROM usersWHERE ageIN (18,21,25);

NOT IN

SELECT*FROM usersWHERE ageNOTIN (18,21);

6. BETWEEN

Used to filter values within a range (inclusive).

SELECT*FROM users
WHERE ageBETWEEN18AND30;

Works with numbers, dates, and timestamps.

7. DISTINCT

Removes duplicate values from the result.

SELECTDISTINCT age FROM users;

👉 Helps in getting unique records.

8. ORDER BY

Sorts data in ascending or descending order.

SELECT*FROM usersORDERBY ageASC;
SELECT*FROM usersORDERBY ageDESC;

9. LIMIT

Restricts the number of rows returned.

SELECT*FROM users LIMIT10;

Often used with pagination.

10. LIKE – Pattern Wise Searching

Used for searching patterns in text.

Wildcards

Examples:

SELECT*FROM usersWHERE nameLIKE'V%';-- Starts with V
SELECT*FROM usersWHERE nameLIKE'%it';-- Ends with it
SELECT*FROM usersWHERE nameLIKE'_i%';-- Second letter is i

11. Aggregate Functions

Aggregate functions perform calculations on multiple rows.

Example:

SELECTAVG(salary)FROM employees;

12. GROUP BY

Groups rows with the same values into summary rows.

SELECT department,COUNT(*)
FROM employees
GROUPBY department;

GROUP BY is often used with aggregate functions.

Example: GROUP BY with HAVING

SELECT department,AVG(salary)
FROM employees
GROUPBY department
HAVINGAVG(salary)>50000;

HAVING filters groups, while WHERE filters rows.

While basic SQL operations like SELECT, INSERT, and UPDATE are easy to learn, PostgreSQL offers many advanced features that can make your queries more efficient, flexible, and powerful.

In this article, we’ll explore 10 essential PostgreSQL concepts that every developer should know:

1. String Functions

2. CASE Statements

3. Relationships

4. Joins

5. Aliases

6. COALESCE

7. User-Defined Functions (UDFs)

8. Window Functions

9. Common Table Expressions (CTEs)

10. Triggers

By the end, you’ll not only understand these features but also know how to apply them in real-world scenarios.

1. String Functions

String functions allow you to manipulate text in PostgreSQL. They are extremely useful when you need to clean, format, or transform data.

Common Functions:

• UPPER(column) → Convert text to uppercase.

• LOWER(column) → Convert text to lowercase.

• CONCAT(str1, str2) → Join multiple strings.

• SUBSTRING(column FROM start FOR length) → Extract a part of a string.

• TRIM(column) → Remove leading/trailing spaces.

• POSITION(substring IN column) → Find the position of a substring.

Example:

SELECT UPPER(first_name) AS uppercase_name,
       SUBSTRING(first_name FROM 1 FOR 3) AS short_name
FROM employees;

Tip: String functions can also be combined to create more complex transformations. For example, you can TRIM spaces and then convert text to UPPERCASE.

2. CASE Statement

The CASE statement in PostgreSQL allows you to implement conditional logic, similar to IF-ELSE statements in programming languages. This is particularly useful for categorizing data based on certain conditions.

Example:

SELECT name,
       CASE 
           WHEN salary > 50000 THEN 'High'
           WHEN salary BETWEEN 30000 AND 50000 THEN 'Medium'
           ELSE 'Low'
       END AS salary_grade
FROM employees;

This query categorizes employees into salary grades without altering the data in the table.

Tip: CASE can be used in SELECT, WHERE, and even ORDER BY clauses for dynamic behavior.

3. Relationships in PostgreSQL

In relational databases like PostgreSQL, tables often relate to each other through keys:

• One-to-One: Each row in Table A corresponds to exactly one row in Table B.

• One-to-Many: One row in Table A can relate to multiple rows in Table B. This is the most common relationship.

• Many-to-Many: Multiple rows in Table A relate to multiple rows in Table B, usually through a junction table.

Example: A departments table (one) and employees table (many) would have a foreign key in employees referencing departments.

4. Joins

Joins are used to retrieve related data from multiple tables. PostgreSQL supports all standard SQL joins:

• INNER JOIN: Returns only rows that match in both tables.

• LEFT JOIN: Returns all rows from the left table, and matching rows from the right.

• RIGHT JOIN: Returns all rows from the right table, and matching rows from the left.

• FULL OUTER JOIN: Returns all rows from both tables.

Example:

SELECT e.name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id;

Tip: Use aliases (e and d in this example) to make queries cleaner and easier to read.

5. Aliases

Aliases give temporary names to tables or columns, improving query readability and reducing typing.

Column Alias Example:

SELECT name AS employee_name, salary AS emp_salary
FROM employees;

Table Alias Example:

SELECT e.name, d.department_name
FROM employees AS e
JOIN departments AS d ON e.department_id = d.department_id;

Tip: Aliases are essential when working with complex joins, especially if multiple tables have columns with the same name.

6. COALESCE

COALESCE is used to handle NULL values. It returns the first non-NULL value from a list of arguments.

Example:

SELECT name, COALESCE(phone_number, 'N/A') AS contact
FROM employees;

Tip: COALESCE is particularly useful in reporting or user-facing applications where you want to avoid displaying NULL values.

7. User-Defined Functions (UDFs)

PostgreSQL allows you to create custom functions that can be reused in queries. This reduces redundancy and improves maintainability.

Example:

CREATE OR REPLACE FUNCTION get_fullname(first VARCHAR, last VARCHAR)
RETURNS VARCHAR AS $$
BEGIN
    RETURN first || ' ' || last;
END;
$$ LANGUAGE plpgsql;

SELECT get_fullname('Vinit', 'Rokade');

Tip: Functions can be used anywhere a regular expression or calculation is needed, making your queries more modular.

8. Window Functions

Window functions allow you to perform calculations across a set of table rows related to the current row, without collapsing the result set.

Example:

SELECT name, department_id, salary,
       RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dept_rank
FROM employees;

This query ranks employees by salary within each department, which is extremely useful for analytics and reporting.

9. Common Table Expressions (CTEs)

CTEs allow you to create temporary named result sets that can be referenced within a query. PostgreSQL also supports recursive CTEs, which are useful for hierarchical data.

Non-Recursive Example:

WITH dept_avg AS (
    SELECT department_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
)
SELECT e.name, e.salary, d.avg_salary
FROM employees e
JOIN dept_avg d ON e.department_id = d.department_id;

Recursive Example (Hierarchy):

WITH RECURSIVE subordinates AS (
    SELECT emp_id, manager_id, name
    FROM employees
    WHERE manager_id IS NULL
    UNION
    SELECT e.emp_id, e.manager_id, e.name
    FROM employees e
    INNER JOIN subordinates s ON e.manager_id = s.emp_id
)
SELECT * FROM subordinates;

Tip: Recursive CTEs are perfect for organizational charts, folder hierarchies, or graph-like structures.

10. Triggers

Triggers automatically execute a function when a specific event occurs, such as INSERT, UPDATE, or DELETE.

Example:

CREATE OR REPLACE FUNCTION log_salary_change()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO salary_log(emp_id, old_salary, new_salary, changed_at)
    VALUES (OLD.emp_id, OLD.salary, NEW.salary, NOW());
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER salary_update
AFTER UPDATE OF salary ON employees
FOR EACH ROW
EXECUTE FUNCTION log_salary_change();

Tip: Triggers are powerful for auditing, logging, or enforcing business rules automatically.

Conclusion

PostgreSQL is more than just a relational database—it’s a powerful platform for building complex, efficient, and scalable applications. Mastering features like string functions, CASE statements, joins, aliases, COALESCE, UDFs, window functions, CTEs, and triggers will give you a strong foundation to write efficient queries, simplify complex logic, and handle real-world data challenges.

Whether you’re a developer, data analyst, or aspiring database engineer, these PostgreSQL tools will help you unlock the full potential of your database.