Getting started with postgres

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.

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.

Installation

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

Login

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

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';

Granting Privileges

We can grant permissions or privileges to the user.

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

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;

CRUD:

Create

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

CREATE TABLE users(
id INT NOT NULL,
first_name VARCHAR(150),
last_name VARCHAR(150),
email VARCHAR(50),
age INT
);

To view all Tables.

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

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','jeegu@yopmail.com', 31);

Read

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

SELECT * FROM users;

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;

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

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

Update

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;

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 TABLE users RENAME COLUMN age TO bio_age;

DELETE

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;

We can drop the table in postgres.

DROP TABLE table_name

DROP TABLE users CASCADE;

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

Conclusion:

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.