Yogesh Chauhan's Blog

What is PostgreSQL? How similar or different it is from SQL?

in Postgres on April 23, 2020

PostgreSQL is a powerful, open source object-relational database system that uses and extends the SQL language combined with many features. It is free and open-source. It supports both SQL for relational and JSON for non-relational queries. 

PostgreSQL was developed based on POSTGRES 4.2 at Berkeley Computer Science Department, University of California.

It is designed to handle a range of workloads, from single machines to data warehouses or Web services with many concurrent users. PostgreSQL was designed to run on UNIX-like platforms. It is the default database for macOS Server, and is also available for Linux, FreeBSD, OpenBSD, and Windows.

AWS supports PostgreSQL through a fully managed database service with Amazon Relational Database Service (RDS). Amazon Aurora with PostgreSQL compatibility is also built using PostgreSQL.

Let's look at Postgre Queries and SQL queries

Select Query Syntax in SQL


SELECT column1, column2, ...
FROM table_name;

OR 

SELECT * FROM table_name;

Select Query Syntax in Postgre – SAME


SELECT column1, column2, ...
FROM table_name;

OR 

SELECT * FROM table_name;

Insert Query Syntax in SQL


INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

Insert Query Syntax in Postgre – SAME


INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

Update Query Syntax in SQL


UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Update Query Syntax in Postgre – SAME


UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

So, basically 

SQL and PostgreSQL both have a similar syntax.

PostgreSQL just have certain extra functionalities that are not there in SQL. For example, it supports many advanced types such as array, hstore, and user-defined type. It also supports IP Address Data type, partial and bitmap indexes, "advanced" triggers (just have wide ranges to set triggers- NOTHING FANCY), CASCADE operations, extensions and custom modules, unicode characters, and our beloved JSON.

Some differences

There are many small differences in theories but I am just writing down some key differences only.

Although, there is no TOP keyword in Postgre. 


SELECT TOP 3 * FROM table;

the above query is not possible in Postgre. Instead we need to write the following query:


SELECT * FROM table LIMIT 3;

Also, LIKE statements are case sensitive in postgresql. The key word ILIKE can be used instead of LIKE to make the match case-insensitive according to the active locale. This is not in the SQL standard but is a PostgreSQL extension.

The plus operator cannot be used for concatenation in Postgre. Instead we use || (double pipe operator).


Most Read

#1 How to check if radio button is checked or not using JavaScript? #2 Solution to “TypeError: ‘x’ is not iterable” in Angular 9 #3 How to add Read More Read Less Button using JavaScript? #4 How to uninstall Cocoapods from the Mac OS? #5 PHP Login System using PDO Part 1: Create User Registration Page #6 How to Use SQL MAX() Function with Dates?

Recently Posted

#Aug 15 Is PHP still good for back-end programming? #Aug 10 How to create a multisite network in WordPress? #Aug 3 How to create a circle that follows a cursor using JavaScript and CSS? #Aug 3 How to make a curtain slider using jQuery and CSS? #Aug 2 How to progressively load images and add a blurry placeholder? #Aug 1 How to create a placeholder loader (throbber) using CSS?
You might also like these
Introduction to Angular modules Part 3: NgModules vs JavaScript modules and Angular librariesAngularURL paths in DrupalDrupalHow to add transparent text on top of an image using CSS?CSSinclude, include_once, require, require_once in PHPPHPHow to get current timestamp in Swift 4 and 5?SwiftGap in Flex?CSS