YogeshChauhan . com

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

amazon

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 How to Use SQL MAX() Function with Dates? #6 PHP Login System using PDO Part 1: Create User Registration Page

Recently Posted

Jun 16 What are Stored Procedures for SQL Server? Jun 16 What are Class Constants in PHP? Jun 15 A short basic guide on states in React Jun 15 How to define constants in PHP? Jun 15 How to define visibility for a property in PHP? Jun 15 How to use @if and @else in SCSS?

You might also like these

CSS Overflow Property with ExamplesCSSUPDATE and DELETE Statements in PostgresPostgresValues and Types Basics in JavaScriptJavaScriptWhat’s the difference between variables in CSS and SCSS (Sass)?CSSWhat is XP (Extreme Programming) and When should it be used?MiscHow to change perception and behavior of a person for acceptance of new systems?Misc