PostgreSQL: A Beginner's Guide to Getting Started

If you're learning backend development, one database you should definitely know is PostgreSQL.

PostgreSQL is a powerful open-source relational database management system (RDBMS) that uses SQL to store, manage, and retrieve data.

What is PostgreSQL?

PostgreSQL is a relational database where data is stored in tables made up of rows and columns.

For example, an employee table might contain:

id	name	age
1	Adhi	21
2	Ravi	24
3	Kumar	22

Each row represents an employee, while each column represents a particular piece of information.

DBMS vs RDBMS

DBMS stands for Database Management System.

RDBMS stands for Relational Database Management System.

An RDBMS stores data in related tables and allows us to establish relationships between them.

PostgreSQL is an RDBMS.

Creating a Database

We can create a database using:

CREATE DATABASE company;

After creating it, we can connect to the database and create tables.

Creating a Table

CREATE TABLE employee (
    id INT,
    name VARCHAR(50),
    age INT
);

Here:

• id stores the employee ID
• name stores the employee name
• age stores the employee's age

Inserting Data

To insert a row:

INSERT INTO employee
VALUES (1, 'Adhi', 21);

We can insert another employee:

INSERT INTO employee
VALUES (2, 'Ravi', 24);

Reading Data

To retrieve all employees:

SELECT * FROM employee;

To retrieve only the names:

SELECT name FROM employee;

Filtering Data

The WHERE clause is used to filter records.

SELECT * FROM employee
WHERE age = 21;

We can also use conditions such as:

SELECT * FROM employee
WHERE age > 21;

Updating Data

To change existing data:

UPDATE employee
SET name = 'Rahul'
WHERE id = 2;

Always be careful with UPDATE. Using it without a WHERE condition can change every row.

Deleting Data

To delete a particular employee:

DELETE FROM employee
WHERE id = 2;

To delete all rows while keeping the table:

DELETE FROM employee;

To delete the entire table:

DROP TABLE employee;

Altering a Table

We can add a new column using:

ALTER TABLE employee
ADD COLUMN salary INT;

We can remove a column using:

ALTER TABLE employee
DROP COLUMN salary;

We can rename a table:

ALTER TABLE employee
RENAME TO employees;

And rename a column:

ALTER TABLE employee
RENAME COLUMN name TO full_name;

Sorting Data

ORDER BY is used to sort results.

Ascending order:

SELECT * FROM employee
ORDER BY age ASC;

Descending order:

SELECT * FROM employee
ORDER BY age DESC;

Aggregate Functions

PostgreSQL provides useful functions for calculations.

Count

SELECT COUNT(*) FROM employee;

Average

SELECT AVG(age) FROM employee;

Maximum

SELECT MAX(age) FROM employee;

Minimum

SELECT MIN(age) FROM employee;

Sum

SELECT SUM(salary) FROM employee;

Important PostgreSQL Topics to Learn

After learning these basics, you should move on to:

1. Primary Keys
2. Foreign Keys
3. Constraints
4. Joins
5. GROUP BY
6. HAVING
7. Subqueries
8. CTEs
9. Indexes
10. Transactions
11. Views
12. PostgreSQL data types
13. JSON/JSONB
14. Query optimization

Conclusion

PostgreSQL is an important skill for backend and full-stack developers.

The best way to learn it isn't by memorizing commands. Create a small database, insert data, query it, update it, delete it, and practice different SQL problems.