Skip to main content


Showing posts from August, 2019

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 …

PostgreSQL- Common Table Expression

Common Table Expression(CTEs) is a handy feature in PostgreSQL, it is a temporary result set that you can reference within another SELECT, INSERT, UPDATE, or DELETE statement. It provides a convenient way to manage the completed queries. CTEs are defined within the PostgreSQL statement using the WITH clause. It resolves many difficulties we face in PostgreSQL query statement such as removes the complexity of sub-query, which executed multiple times, equally helpful in place of temporary tables.
In this article, we will learn the uses of Common Table Expression based on the scenario. The CTEs start with WITH clause and followed by the expression name. Further we use the expression name in a select query to obtain the records of Common Table Expression. Syntax of WITH clause in PostgreSQL
WITH CTE_Name [ ( column_name [,...n] ) ] AS ( CTE_query_definition )
 For example, suppose we have the Employee table that contains employee details with managerid.
CREATE TABLE public.employee ( id int…

LIMIT and OFFSET Clause in PostgreSQL

It can be troublesome to obtain records from a table that contains large amounts of data. Obtaining large amounts of data from a table via a PostgreSQL query can be a reason for poor performance. Using LIMIT and OFFSET we can shoot that type of trouble. LIMIT Clause is used to limit the data amount returned by the SELECT statement while OFFSET allows retrieving just a portion of the rows that are generated by the rest of the query. Syntax: SELECT statement with LIMIT clause
SELECT Col_1, Col_2, ...Col_3 FROM table_name LIMIT number_of_rows
Syntax: SELECT statement with LIMIT clause and OFFSET clause
SELECT Col_1, Col_2, ...Col_3 FROM table_name LIMIT number_of_rows OFFSET row_number

ExamplesSuppose we have a customer table which has a large amount of data, using the following the query you can create the customer table schema.
CREATE TABLE public."Customer" ( "Id" serial NOT NULL, "FName" text COLLATE pg_catalog."default", "LName" t…