PostgreSQL is a powerful, open-source, object-relational database system. It uses SQL to handle data workloads.
PostgreSQL (or Postgres) is a free and open-source relational database management system.
It is a stable and reliable database system that is developed by the open-source community.
Postgres is used as the primary database for many web, mobile, and analytics applications.
Postgres can be downloaded from this link.
The PostgreSQL project started in 1986 at Berkeley Computer Science Department, University of California. It was originally called POSTGRES, in reference to the older Ingres database. The goal of the project was to add minimal features to support multiple data types.
In 1996, the POSTGRES project was renamed to PostgreSQL to highlight its support for SQL. Since then, the PostgreSQL Global Development Group, a dedicated community of contributors continues to make the releases of the open-source and free database project.
Originally, PostgreSQL was designed to run only on UNIX-like platforms, but it has evolved and today it supports multiple platforms including Windows, Mac, and Solaris.
PostgreSql is a good choise in these scenarios.
Dynamic web applications that require a robust back-end databases.
These include business web solutions, mobile applications, APIs and other products.
Postgres also supports geospatial data types using the PostGIS extension.
This is great for building GIS solutions (GIS = geographic information systems).
Various data types: primitives, structured, document, geometry, customizations
Suppors SQL data integrity such as UNIQUE, primary and foreign keys, and more
Allows concurrency and has efficient database performance
Supports disaster recovery
Implements different security protocols such as authentication and access-control system
Has many extensions that provide additional functions, such as PostGIS
Supports full text search
PostgreSQL is available for various platforms as well as the source code.
For Linux downloads, PostgreSQL can be downloaded here and OS version can be selected as well.
For MacOS, PostgreSQL can be downloaded from different sources.
This link allows you to download PostgreSQL on Windows.
For BSD operating system, the installer can be downloaded here.
Homebrew is a Mac OS X package manager that allows you to build Postgres software.
This package manager includes a PostgreSQL version called formula.
This tool is primarily for developers who prefer command line installation.
PostgreSQL uses INSERT INTO
statement to add new rows into table.
Single or multiple rows may be added during the insert.
Syntax for INSERT INTO
statement.
INSERT INTO table-name (column1, column2, ..., columnn) VALUES (value1, value2, ..., valuen);
column1, column2, ..., columnn
-- database columns.
value1, value2, ..., valuen
-- values to assign to each column.
The column names can be skipped if values are added for all table columns.
The order of the values must be the same as the order of the column.
INSERT INTO table-name VALUES (value1, value2, ..., valuen);
Add a customer to the Customer table.
INSERT INTO Customer (FirstName, LastName, Country, City, Phone) VALUES ("Harold", "Smith", "New York", "USA", "235 969 6845");
Add multiple customers to the Customer table.
INSERT INTO Customer (FirstName, LastName, Country, City, Phone) VALUES ("Harold", "Smith", "New York", "USA", "235 969 6845"), VALUES ("Allan", "Martinez", "Houston", "USA", "444 879 2102"), VALUES ("Joan", "Johnsons", "Boston", "USA", "144 874 1025");
PostgreSQL uses the UPDATE
statement to modify data in a table.
Data can be updated based on the specified condition.
Syntax for UPDATE
statement.
UPDATE table-name SET column1 = value1, column2 = value2, ..., columnn = valuen, [WHERE condition];
Replace USA with United States in the Customer table.
UPDATE Customer SET Country = "United States" WHERE Country = "USA";
PostgreSQL uses INSERT ON CONFLICT
to perform upsert (insert or update).
It checks if the row being inserted already exists, if yes, it will update the record instead.
ON CONFLICT
is supported starting with PostgreSQL 9.5.
Syntax for INSERT ON CONFLICT
statement.
INSERT INTO table-name (column-list) VALUES (value-list) ON CONFLICT target action;
target
-- a column, contraint name, or WHERE
clause with predicate.
action
-- DO NOTHING
or DO UPDATE
statements.
Add a new record on the Supplier table only if the CompanyName does not exist yet.
INSERT INTO Supplier (CompanyName, Email, Phone) VALUES ('Hot Beverages','info@hotbeverages.com', '312 490 3003') ON CONFLICT (CompanyName) DO NOTHING;
Python has various dedicated database drivers for PostgreSQL.
One of them is the psycopg database adapter.
This adapter connects your python application to a PostgreSQL database.
This example code connects to a database.
conn = psycopg2.connect(host="database-server", database="database-name", user="username", password="password")
database-server
-- database server such as localhost or an IP address.
database-name
-- the name of the database to connect to.
username
-- username for authentication.
password
-- password for authentication.
PostgreSQL supports single and multidimensional arrays as data type.
Array is any built-in or user-defined base, enum, or composite type.
Arrays can be defined in TEXT
, TEXT[]
, INTEGER
, INTEGER[]
, BIT
, and BIT[]
, and other types.
This Customer table accepts multiple values on Address.
CREATE TABLE Customer ( Id SERIAL PRIMARY KEY NOT NULL, FullName VARCHAR(250) NOT NULL, Address TEXT[] );
An alternate method to create a table with an array.
CREATE TABLE Customer ( Id SERIAL PRIMARY KEY NOT NULL, FullName VARCHAR(250) NOT NULL, Address TEXT ARRAY );
There are two ways to insert array values.
One way is by adding the values inside a comma-separated string value.
'{value1, value2, ..., valuen}'
The other way is by adding values inside an ARRAY[]
.
ARRAY[value1, value2, ..., valuen]
Examples of adding multiple address values to a Customer record.
INSERT INTO Customer (FullName, Address) VALUES ('Harold Smith', '{"Boston, USA", "Seattle, USA", "Austin, USA"}') INSERT INTO Customer (FullName, Address) VALUES ('Harold Smith', ARRAY['Boston, USA', 'Seattle, USA', 'Austin, USA'])
Return a list of Customers with their address(es).
SELECT * FROM Customer
Id | FullName | Address |
---|---|---|
1 | Harold Smith | Boston, USA,"Seattle, USA,"Austin, USA |
PostgreSQL replaces spaces with double-quotes.
This is removed when a specific value is selected and will not cause any error or problem.
Return the list of Customers and their first address specified.
SELECT FullName, Address[1] FROM Customer
FullName | Address |
---|---|
Harold Smith | Boston, USA |
A value range can also be selected from the array value.
This query returns the first and second address provided by Customer.
SELECT FullName, Address[1:2] FROM Customer
FullName | Address |
---|---|
Harold Smith | Boston, USA,"Seattle, USA |
Update all address values for Customer with Id = 1.
UPDATE Customer SET Address = ARRAY['Boston, USA', 'Seattle, USA', 'Austin, USA'] WHERE Id = 1;
Update the first address for ustomer with Id = 1.
UPDATE Customer SET Address[1] = 'Denver, USA' WHERE Id = 1;
Update the first and second address for Customer with Id = 1.
UPDATE Customer SET Address[1:2] = ARRAY['Denver, USA', 'Chicago, USA'] WHERE Id = 1;
JSON
and JSONB
are used to store JSON data on PostgreSQL.
JSON
stores the exact copy of the JSON input text as ASCII/UTF-8 string.
JSONB
stores the JSON input in binary form.
Create an Inventory table with all the Supplier and their Products listed.
CREATE TABLE Inventory ( Supplier VARCHAR(250) NOT NULL, Products JSON )
Using the Inventory table, add JSON data for Products.
INSERT INTO Inventory VALUES ('Hot Beverages', '{"category": "Chocolate Drink", "name": ["Brownie Cholocate", "Hot chocolate", "Ovaltine"], "isActive": false}');
Return all products from Hot Beverages supplier.
SELECT Products->'name' AS 'Product Name' FROM Inventory
Product Name |
---|
Brownie Cholocate |
Hot chocolate |
Ovaltine |
PostgreSQL CASE
is like a long IF/ELSE
statement.
This statement allows you to add conditional statements.
Syntax for WHEN CASE
statement.
CASE expression WHEN value1 THEN result1 WHEN value2 THEN result2 WHEN ... THEN ... WHEN valuen THEN resultn ELSE else-result END
List products and whether they are Expensive (>= $100) or Cheap (< $100).
SELECT ProductName, CASE Price WHEN >= 100 THEN 'Expensive' WHEN < 100 THEN 'Cheap' END PriceCategory FROM Product
PostgreSQL GRANT
statement gives privileges to specific database roles.
Privileges can be granted to SELECT
, INSERT
, UPDATE
, DELETE
, TRUNCATE
, and more.
The ALL
keyword can be used to grant all database privileges.
Syntax for GRANT
statement.
GRANT privilege-list | ALL ON table-name TO role-name;
Both PostgreSQL and MySQL are open-source and both can compete with enterprise solutions. However, there are major differences as listed below.
Description | PostgreSQL | MySQL |
---|---|---|
Popularity | Less popular, but more advanced | Most popular |
Architecture | Object relational and multiprocess | Relational and single process |
Complexity | Handles complex queries and massive databases | Easy to set up and manage, fast, reliable, and well-understood |
Data types | Numeric, date/time, character, boolean, enumerated, geometric, network address, JSON, XML, HSTORE, arrays, ranges, composite | Numeric, date/time, character, spatial, JSON |
Index support | B-tree, hash, GiST, SP-GiST, GIN, and BRIN | Primarily B-tree; R-tree, hash, and inverted indexes for certain data types |
Suitable for | High volume of both reads and writes | High volume of reads |
Security | Access control, multiple encrypted connection options | Access control, encrypted connections |
Support | Community support. Companies that have their own release of PostgreSQL may offer support around it. | Community support, vendor-provided support contracts |
GUI Tool | PgAdmin | MySQL Workbench |
Free and open-source
Highly expandable
Largely compliant with the SQL standard
Possible to process complex data types such as geographical
Flexible full text search
Supports creating custom functions, triggers, data types, and more
Supports many programming languages
Supports JSON
Cross-platform