CRUD Operations in PostgreSQL: A Step-by-Step Guide

PostgreSQL is a powerful, open source object-relational database system that uses and extends the SQL language combined with many features that safely store and scale the most complicated data workloads.

CRUD Operations: The Building Blocks of Database Management

CRUD operations are the fundamental actions that can be performed on data in a database. They are the building blocks of database management and are essential for managing and manipulating data in a database. The four basic CRUD operations are:

• Create: Inserting new data into a database.

• Read: Retrieving existing data from a database.

• Update: Modifying existing data in a database.

• Delete: Removing data from a database.

Let's create a new table 'students' in the Database

CREATE TABLE students (
  name CHAR(255),
  age INT,
  branch VARCHAR(100),
  year INT
);

You will get a confirm message in the psql terminal after creating a table as : CREATE TABLE

name CHAR(255)

This line defines a column named "name" with a data type ofCHAR(255). This means that the "name" column can store strings of up to 255 characters.

age INT

This line defines a column named "age" with a data type ofINT. This means that the "age" column can store integer values.

branch VARCHAR(100)

This line defines a column named "branch" with a data type ofVARCHAR(100). This means that the "branch" column can store strings of up to 100 characters.

year INT

This line defines a column named "year" with a data type ofINT. This means that the "year" column can store integer values.

1. Create

The create operation is used to insert new data into a database. This operation is essential for populating a database with initial data or for adding new records to an existing database.

Syntax:

INSERT INTO table_name (column1, column2, …)
VALUES (value1, value2, …);

Example:

INSERT INTO students (name,age,branch,year) 
values ('jay',22,'Computer Science',4);

We can also insert multiple data by following syntax:

INSERT INTO table_name (column1, column2, …)
VALUES (value1, value2, …), (value1,value2,....),......

Example:

INSERT INTO students (name,age,branch,year) 
values('janhvi',21,'Electrical',3),
('Aditya',22,'Civil',4);

2. Read

TheREADoperation is used to retrieve existing data from a database. This operation is essential for querying the database to retrieve specific data or for displaying data to users.

Let's read the above inserted data into the table.We can either retrieve data from all the columns or specific ones.

Syntax:

SELECT * FROM table_name;
SELECT col1,col2... FROM table_name;

1. Reading Data from all the columns

Example:

SELECT * FROM students;

Output:

name  | age |      branch      | year 
--------+-----+------------------+------
 jay    |  22 | Computer Science |    4
 janhvi |  21 | Electrical       |    3
 Aditya |  22 | Civil            |    4

2. Reading Data from specific the columns

SELECT name FROM students;

Output:

name  
--------
 jay
 janhvi
 Aditya

3. Update

TheUPDATEoperation is used to modify existing data in a database. This operation is essential for updating records in a database or for changing the values of specific columns. Here's an example of how to update a record in a table:

Syntax:

UPDATE table_name
SET column1 = value1,
    column2 = value2,
    ...
WHERE condition;

Example:

UPDATE students
SET branch = 'Medical'
WHERE name = 'janhvi';

Let's see the changes in the table by SELECT query:

select * from students;
  name  | age |      branch      | year | city 
--------+-----+------------------+------+------
 jay    |  22 | Computer Science |    4 | 
 Aditya |  22 | Civil            |    4 | 
 janhvi |  21 | Medical          |    3 |

The branch of student Janhvi is changed to Medical.

4. Delete

TheDELETEoperation is used to remove data from a database. This operation is essential for deleting records from a database or for removing unnecessary data.

Syntax:

DELETE FROM table_name
WHERE condition;

Example:

DELETE FROM students
WHERE name = 'Aditya';

select * from students;
  name  | age |      branch      | year 
--------+-----+------------------+------
 jay    |  22 | Computer Science |    4
 janhvi |  21 | Medical          |    3

The data of the student Aditya is deleted

Conclusion

In this blog, we have explored the basics of PostgreSQL and how to create a table, insert data, update data, and delete data.

I hope that this blog has been helpful in introducing you to the world of PostgreSQL and providing you with the necessary knowledge to get started.

Don't forget that practice makes perfect, so feel free to play around and experiment with the commands in this blog.

PRACTICE PRACTICE PRACTICE! ✍

Thanks for reading!