Skip to main content

Posts

JOINS in PostgreSQL

As per the requirement of the project data, normalization, and table relationships are two factors that come to our mind when creating the database schema, it means we minimize the redundancy from a relation or set of relations, and for the referential integrity between them, we draw the relationship. For the user-friendly application's interface when we obtain the records from the database sometimes we need to combine the column from one (self-join) or more tables based on the values of the common columns between the tables. Using PostgreSQL JOIN we combine the column from two or more tables, based on a related column between them. JOINs available in PostgreSQL are:- 1- INNER JOIN                   2- LEFT JOIN 3- RIGHT JOIN 4- FULL OUTER JOIN 5- CROSS JOIN 6- NATURAL JOIN 7- SELF-JOIN To understand the concept of the JOIN I am creating the following table and make a relationship between them.
-- Employee table CREATE TABLE public.employee (     empid serial NOT NULL,-- primary …

Constraints in PostgreSQL

Constraints are some predefined rules applied to columns that are followed to maintain the accuracy and reliability of data. Data Type is self a constraint in PostgreSQL, you can't put string data in INT data type column. The constraints applied to the columns while creating the table, means constraint create while creating a table. Constraints can be on column level or table level, column level constraint applied on particular column and table level constraint applied on whole table. Constraints available in PostgreSQL are described below. 1- Primary Key Constraint 2- Unique Key Constraint 3- Not Null Constraint 4- Check Constraint 5- Foreign Key Constraint 6- Exclusion Constraints 1- Primary Key ConstraintWe use the primary key constraint on the column to uniquely identify the row in a database table. It can't be null and every value of the column should be unique. The primary key has a crucial role in database schema design, we use them to make relation from one table to another …