PostgreSql Database

PostgreSQL is a powerful, open-source, object-relational database system. It uses SQL to handle data workloads.

SQL

PostgreSQL

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.

History of PostgreSQL

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.


Use Cases

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


Features of PostgreSQL

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 Download

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.


PostgreSQL Homebrew

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 Insert

PostgreSQL uses INSERT INTO statement to add new rows into table.

Single or multiple rows may be added during the insert.

Syntax

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

Example

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 Update

PostgreSQL uses the UPDATE statement to modify data in a table.

Data can be updated based on the specified condition.

Syntax

Syntax for UPDATE statement.

UPDATE table-name
  SET column1 = value1,
      column2 = value2,
      ...,
      columnn = valuen,
 [WHERE condition];

Example

Replace USA with United States in the Customer table.

UPDATE Customer
   SET Country = "United States"
 WHERE Country = "USA";

PostgreSQL Upsert

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

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.

Example

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;

PostgreSQL Python

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 Array

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.

Create a table with Array data type

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
);
Note: PostgreSQL Array is 1-based which means instead of the index starting at 0, it starts at 1.

Insert Array data

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'])

Selecting Array data

Return a list of Customers with their address(es).

SELECT * 
  FROM Customer
Result: 1 record
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
Result: 1 record
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
Result: 1 record
FullName Address
Harold Smith Boston, USA,"Seattle, USA

Modifying Array data

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;

PostgreSQL JSON and JSONB

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 a table with a JSON data type

Create an Inventory table with all the Supplier and their Products listed.

CREATE TABLE Inventory 
(  
  Supplier VARCHAR(250) NOT NULL,
  Products JSON
)

Insert JSON data

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}');  

Display JSON data

Return all products from Hot Beverages supplier.

SELECT Products->'name' AS 'Product Name' 
  FROM Inventory
Result: 3 records
Product Name
Brownie Cholocate
Hot chocolate
Ovaltine

PostgreSQL When Case

PostgreSQL CASE is like a long IF/ELSE statement.

This statement allows you to add conditional statements.

Syntax

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

Example

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

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

Syntax for GRANT statement.

GRANT privilege-list | ALL 
   ON table-name
   TO role-name;

PostgreSQL vs MySQL

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

Advantages of PostgreSQL

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


Disadvantages of PostgreSQL

  • Not available on all hosts by default
  • Documentation is only available in English
  • Comparatively low reading speed
  • Horizontal scaling is not possible
  • No column re-ordering

You may also like



Guides


vsn 3.1