Introduction to PostgreSQL and PostgreSQL CTE, Upsert

This blog will discuss PostgreSQL and the uniqueness of PostgreSQL as compared to other open-source SQL software. Let us explore PostgreSQL Common Table Expressions (CTE), PostgreSQL Upsert (Update+Insert), and much more.

Introduction to PostgreSQL

PostgreSQL is a relational database management system that is sophisticated, enterprise-class, and open-source. PostgreSQL supports both relational and non-relational data querying. It is a robust database that the open-source community has developed for more than two decades.

Many online apps, mobile, and analytics applications use PostgreSQL as their primary database.

Uniqueness of PostgreSQL

PostgreSQL is an object-relational database. It offers an advantage over MySQL, MariaDB, and Firebird, all open-source SQL databases. It supports many data types as described below:

JSON support: The JSON support in PostgreSQL allows you to use a SQL database without a schema. It is beneficial when the data structure requires some flexibility due to ongoing development or when the data fields that the data object will include are unknown. The JSON data type enforces proper JSON, allowing you to query and manipulate the data with the specific JSON operators and functions included in PostgreSQL.
Geometric Data: Geo-data is quickly becoming a requirement for many applications. Geometric data types such as points, lines, circles, and polygons have long been supported by PostgreSQL. One of these is the PATH data type. A path is a series of points that might be open (the starting and end points are not connected) or closed (the beginning and end points are connected) (the beginning and end points are connected).
Network Address: Different network address types can be stored in PostgreSQL. The CIDR data type (Classless Internet Domain Routing) follows the IPv4 and IPv6 network address conventions. The INET data type, which is used for IPv4 and IPv6 hosts where the subnet is optional, is also accessible for network address storage. The MACADDR data type can be used to store MAC addresses.

PostgreSQL CTE

The CTE (Common Table Expression) is a temporary result set in PostgreSQL that the user can reference within another SQL operation such as SELECT, INSERT, UPDATE, or DELETE. CTEs are transitory because they exist only while the query is being executed. In PostgreSQL, CTEs are commonly used to simplify complex joins and subqueries.

Syntax:

With CTE_Name (Column_list) as (
CTE_query_statement
)
Statement;

With CTE_Name (Column_list) as (

CTE_query_statement

)

Statement;

The CTE’s name comes first, followed by an optional column list.

Second, specify a query that returns a result set inside the WITH clause’s body. If you do not mention the column list following the CTE name, the CTE query definition’s select list will be used as the CTE’s column list.

Third, treat the CTE like a table or view in the SELECT, INSERT, UPDATE, or DELETE statement.

Example:

With cte_insert as (
Insert into Students(id, name) values (1,’Ravi’) returning id
) 
Select * from Students where id = (select id from cte_insert)

With cte_insert as (

Insert into Students(id, name) values (1,’Ravi’) returning id

)

Select * from Students where id = (select id from cte_insert)

Executing multiple queries using CTE

With cte_std as (
Insert into Students(id, name, courseid) values (2,’Hari’,101) returning id,courseid
), cte_course_insert as (
Insert into Course(sid, cid) values ((select id from cte_std),(select coursed from cte_std))
) 
Select * from Students join Course on Students.id=Course.sid;

With cte_std as (

Insert into Students(id, name, courseid) values (2,’Hari’,101) returning id,courseid

), cte_course_insert as (

Insert into Course(sid, cid) values ((select id from cte_std),(select coursed from cte_std))

)

Select * from Students join Course on Students.id=Course.sid;

In this above query, we insert data into different tables in a single query without using any subqueries, breaking complex things into simpler ones.

PostgreSQL UPSERT

The phrase upsert is also used as a merge in relational databases. If a row already exists in the table, PostgreSQL will update it; otherwise, it will insert the new row. Hence the action is known as upsert (update or insert).

Syntax:

Insert into table_name(col1, col2) 
Values (val1, val2,)
On conflict <colume_name/where_clause>  action;

Insert into table_name(col1, col2)

Values (val1, val2,)

On conflict <colume_name/where_clause> action;

One of the following actions could be taken:

DO NOTHING — If the row already exists in the table, do nothing.

WHERE condition – change some fields in the table. DO UPDATE SET column 1 = value 1

Example:

Create a Table as shown below:
create table doctor (
id integer PRIMARY KEY,
 name varchar(100), 
active varchar(100)
);

Create a Table as shown below:

create table doctor (

id integer PRIMARY KEY,

name varchar(100),

active varchar(100)

);

Now, insert data into it:

insert into doctor (id, name, active) values (1, 'doctor1', 'true');
->select * from doctor;

1 2	insert into doctor (id, name, active) values (1, 'doctor1', 'true'); ->select * from doctor;

Let us check if doctor1 is present then we make him inactive, if he is not there in the table then we will insert a new record and set him too inactive.

insert into doctor(id, name, active) 
 values (1,’doctor1’ ,'false') 
 on conflict (id) 
 do update set active = 'false'
->select * from doctor;

insert into doctor(id, name, active)

values (1,’doctor1’ ,'false')

on conflict (id)

do update set active = 'false'

->select * from doctor;

In the above query, as id=1 already exists, only the update query worked, or else the insert query has worked. The column name we give in on conflict should be either unique or a primary key constraint.

Conclusion

Today we have gone through the Postgre SQL features and its unique characteristics which made Postgre SQL more popular among other open-source SQL software. We have used temporary tables i.e CTEs, which make our query more optimized.

About CloudThat

CloudThat is the official AWS (Amazon Web Services) Advanced Consulting Partner, Microsoft Gold Partner, Google Cloud Partner, and Training Partner helping people develop knowledge of the cloud and help their businesses aim for higher goals using best-in-industry cloud computing practices and expertise. We are on a mission to build a robust cloud computing ecosystem by disseminating knowledge on technological intricacies within the cloud space. Explore our consulting here.

If you have any queries regarding PostgreSQL and PostgreSQL CTE, Upsert, or any other service, drop a line below the comments section. I will get back to you at the earliest.

FAQs

What is PostgreSQL, and what does it cost?

PostgreSQL is an object-relational database system with the functionality of conventional proprietary database systems. The entire source code for PostgreSQL is available for everyone, and it is free.

Where can I get PostgreSQL?

You can obtain the source code from https://www.postgresql.org/ftp/

Can we execute a CTE without a statement in the end?

No, You have to give a statement in the end to execute a CTE query.

Voiced by Amazon Polly

WRITTEN BY Imraan Pattan

Imraan is a Software Developer working with CloudThat Technologies. He has worked on Python Projects using the Flask framework. He is interested in participating in competitive programming challenges and Hackathons. He loves programming and likes to explore different functionalities for creating backend applications.