In this post, we will go through some of the basic of PostgreSQL and will perform CRUD operations.

What is PostgreSQL or Postgres?

Postgres is an open-source object-relational database. PostgreSQL supports both SQL (relational) and JSON (non-relational) querying. It is also one of the most used databases in the industry for production and deployment. It uses SQL Structured Query Language to access and manipulate databases.

What is relational database?

You may have encountered this term at your university or in your job as a software engineer.

A relational database is a type of database that stores and provides access to data points that are related to one another. The creation of a relational database requires schema, where defines how the data is organized and how the relations among them are associated.

Relational Database

In the above image, we have a car database in which we have 4 tables (cars, engines, wheels, and pistons). With the help of 3 tables (Engine, wheels, and pistons) we have created cars.

So in every table, we have id which is a primary key. The primary key is the unique key in that table, The id of (Engine, wheels, and pistons) we are using to create the car table, so the primary key of those tables is a foreign key in the car table. This is a basic example of a relationship between tables.

Some Basic Concepts

  • Table: In RDBMS (Relational Database Model) data is organized in the form of row and column. It is the most common and simplest form to store data in a relational database.
  • Column: A column is a vertical entity in a table that contains all information associated with a specific field in a table.
  • Field: It is a column in a table, which holds a specific value in a table.
  • Row: A row of data in each individual entry of a column that exists in the table.

Basic Concepts


You can follow PostgreSQL Downloads according to your operating system to install in your own system.


we can run psql -U postgres to login in Postgres database as a postgres user which we had set up in installing the procedure.

Login Postgres

Create a User

To create a user in Postgres, so we can create a user, with a password also adding the validity of the user.

CREATE USER user_name WITH PASSWORD 'password' VALID UNTIL '2021-01-01';

Create User

Granting Privileges

We can grant permissions or privileges to the user.

Create User

To list all users and their privileges, we can use \du command.

Create User

Remove Privileges

We can also delete users from the database but before deleting a user we have to delete that user has owned (tables, Sequences, and functions, etc).

DROP OWNED BY user_name;
DROP USER user_name;

Create User



Creat to create a table, defining a schema and datatypes of the columns.

first_name VARCHAR(150),
last_name VARCHAR(150),
email VARCHAR(50),
age INT

Create Table To view all Tables.

First select current \c database_name;. Then \dt to list all tables.

List All Database

Insert values in our users table.

INSERT INTO table_name(column1, column2,)
VALUES (value1, value2,);
INSERT INTO users(id, first_name, last_name, email, age) VALUES (2, 'Jignesh', 'Verma','', 31);

Insert Into Table


We can read data from our table by SELECT Query/Statement.

SELECT * FROM users;

Select data from Table

In the above example, we have query all the data from the table. We can filter out with the help of the where clause.

 select id, first_name, age from users where age > 20;

Select data from Table

For sorting, we can use order by column_name DESC and ASC.

SELECT id, first_name, age FROM users ORDER BY id DESC;

Select data from Table


To update any row in our table, UPDATE query to update or manipulate the existing data in our table.

UPDATE table_name SET cloumn = value;
UPDATE users SET first_name = 'Jignesh (JIGU)' where id = 2;

update data psql

We can also modify(add, delete or rename) the column with the help of the ALTER statement.

ALTER TABLE table_name RENAME COLUMN prev_column TO new_column;

alter column psql


DELETE statement allows you to delete one or more rows from a table.

DELETE FROM table_name WHERE condition;
DELETE FROM users WHERE age= 31;

Delete Row psql

We can drop the table in postgres.

DROP TABLE table_name

Delete Table psql

In the above query we have used cascade keywordto remove all dependent objects first before dropping the user table.


In this post, we have implemented CRUD functionality in Postgres. The database plays important role in all the types of development from Web to Application, From Backend to QA. So hope this post helps. Thanks for reading. if you found any issues or you have any query please contact.

Categories: PostgresDatabase