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

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).

Share this post