PostgreSQL: A Concise Tutorial Just An Hour | ACTE
PostgreSQL Tutorial

PostgreSQL: A Concise Tutorial Just An Hour – FREE

Last updated on 02nd Jul 2020, Blog, Tutorials

About author

Buvanesh (Azure DevOps Engineer )

Buvanesh is an Azure DevOps Engineer with ten years of experience in BI and Data Science teams, CI/CD with Git, Jenkins and Azure DevOps, PLSQL Actimize, NoSQL Databases, and Data Modelling in Hive, CDH/HDP, CDH or HDP, Spark, Airflow, NiFi, Kafka, Hive, HBase or MongoDB, Neo4J, Elastic Search, Impala, and Sqoop. He spends his precious time researching various technologies and startups.

(5.0) | 19339 Ratings 2024
PostgreSQL-Tutorial-navigate
  • PostgreSQL is a popular relational database management system (RDBMS). Our PostgreSQL tutorial provides basic and advanced concepts of PostgreSQL.
  • In this tutorial, we are going to learn all the topics of PostgreSQL language, such as create a database, drop a database, select database, select table, update a record, create a table, delete record, drop table, triggers, functions, insert the record, procedures, cursors, etc.
  • We are also going to provide PostgreSQL interview questions and quizzes to help you better understand the PostgreSQL language. This tutorial will help us to understand all the primary features of PostgreSQL, and also provides knowledge on how to use PostgreSQL.

    Subscribe For Free Demo

    [custom_views_post_title]

    What is PostgreSQL?

    PostgreSQL is an ORDBMS [Open-Source Object-Relational Database Management System]. It is used to store data securely; supporting best practices, and allowing recovering them when the request is processed.

    • PostgreSQL was initially introduced on 8th July 1996 at the University of California.
    • It is the first DBMS, which performs MVCC [Multi-Version Concurrency Control] feature, even before Oracle. The multi-version concurrency control attribute is known as snapshot isolation in Oracle.
    • It is written in the C programming language.
    • PostgreSQL is cross-platform and runs on various operating systems such as Microsoft Windows, UNIX, FreeBSD, Mac OS X, Solaris, HP-UX, LINUX, and so on.
    • PostgreSQL is the existing database for the macOS server.
    • PostgreSQL is also pronounced as Post-gress-Q-L, which is developed by the PostgreSQL Global Development Group (a worldwide team of volunteers), any organization or other private entity does not control it.
    • PostgreSQL will offer us the facility to add custom functions with the help of various programming languages such as Java, C, and C++, etc.
    • In this, we can describe our functional languages, index types and data types, and we can also create a custom plugin to increase the reliability of our needs.
    • Its source code is accessible under PostgreSQL license; thus, we can use it freely, change and allocate PostgreSQL in any form.
    • The PostgreSQL follows the transaction along with the ACID (Atomicity, Consistency, Isolation, and Durability) properties.
    • The primary objective of PostgreSQL is to handle a variety of jobs from single technologies to web service or the data warehouse with several parallel users.

    History of PostgreSQL

    • PostgreSQL was developed by the PostgreSQL Global Development Group, which is led by a computer science professor named Michael Stonebraker at the University of California, Berkeley [UCB].
    • The professor’s stonebreaker started in 1986 as a follow-up project and a post-Ingres project to overcome the problems of the existing database system.
    • Initially, it was called Postgres, but in 1996, the project was renamed to PostgreSQL. Then again, in 2007, they did some analysis, and the development team decided to preserve the name as PostgreSQL. And now, it is the most innovative open-source database available in the market.
    PostgreSQL-Tutorial-navigate
    YearsDescription
    1977-1985: In these particular years, the INGRES project is developed.The Ingres Company developed in 1980 and 1994: the computer Associates acquires it.The Ingres project is a proof-of-concept for relational databases.
    1986-1994: POSTGRESIn INGRES, upgrading the features is essential for object-orientation and the query language Quel.The codebase of INGRES was not used as a basis for POSTGRES.After that, it is commercialized as Illustrate, which is acquired by Informix and IBM.
    1994-1995: Postgres95In 1994, support for SQL was added.In 1995, they released as Postgres95.In 1996, they were re-released as PostgreSQL 6.0.The Formation of the PostgreSQL Global Development Team.

    Key features of PostgreSQL

    Procedural languages:

    • PostgreSQL supports four standard procedural languages (which allows the users to write their own code which can be executed by database server) – PL/pgSQL, PL/Tcl, PL/Perl and PL/Python.
    • Besides, other non-standard procedural languages like PL/PHP, PL/V8, PL/Ruby, PL/Java etc. are also supported. But all these and many more non-standard procedural languages need additional packages to be installed, unlike standard procedural languages.

    Indexes:

    • PostgreSQL supports B+-tree, hash, generalized search trees (GiST) and generalized inverted indexes (GIN). Users can also create their own customized indexes.

    Triggers:

    • Triggers, which initializes an action by an SQL Data Management Language statement (DML), mostly by INSERT and UPDATE statements are fully supported.

    Views:

    • Views can create virtual tables. It is supported by PostgreSQL.

    MVCC:

    • Multiversion concurrency control (MVCC) is a system to manage concurrency where each user’s action is not visible to others until a transaction is committed and thus multiple users can work simultaneously.

    Rules:

    • Rules, which allow a hierarchy of an incoming query to be re-written, are supported by PostgreSQL.

    Data Types :

    • The following data types are supported by PostgreSQL : Boolean, Arbitrary precision numeric, Character (text, varchar, char), Binary, Date/time (timestamp/time with/without timezone, date, interval), Money Enum, Bit strings, Text search type, Composite Variable length arrays (including text and composite types) up to 1 GB in total storage size, Geometric primitives, IPv4 and IPv6 addresses, CIDR blocks and MAC addresses, XML supporting XPath queries (as of 8.3), UUID (as of 8.3).

    User defined objects:

    • It supports the creation of almost all new objects inside the database like Casts, Conversions, Data types, Domains, Functions including aggregate functions and window functions, Indexes including custom indexes for custom types Operators.

    Inheritance:

    • In PostgreSQL, a table can be set to inherit their characteristics from a “parent” table.

    Extensions:

    • To add additional functionality to PostgreSQL, several extensions can be installed.

    Other important features:

    • Referential integrity constraints, Inner, outer (full, left and right), and cross joins, Sub-selects, Transactions, SSL, Domains, Tablespaces, Savepoints, Point-in-time recovery, implemented using Write-ahead logging, Two-phase commit, TOAST (The Oversized-Attribute Storage Technique) is used to transparently store large table attributes (such as big MIME attachments or XML messages) in a separate area, with automatic compression, Regular expressions, Common table expressions, Embedded SQL, Full text search, Per-column collation (from 9.1).
    • PostgreSQL supports most of the major features of SQL:2008 standard.

    Tools to manage PostgreSQL

    There are several Open Source as well as Paid tools are available as front-end to PostgreSQL. Here are a few of them which are widely used :

    psql:

    • It is a command line tool and the primary tool to manage PostgreSQL. pgAdmin
    • It is a free and open source graphical user interface administration tool for PostgreSQL.
    Course Curriculum

    Best Advanced Practical Oriented PostgreSQL Certification Course

    Weekday / Weekend BatchesSee Batch Details

    phpPgAdmin:

    • It is a web-based administration tool for PostgreSQL written in PHP. It is based on phpMyAdmin tool to manage MySQL.OpenOffice.org Base
    • It can be used as a front end tool to PostgreSQL.

    Install PostgreSQL on Windows – PostgreSQL Tutorial

    To install PostgreSQL on Windows, you have to follow the below steps:

    Step 1:  Go to the official website of PostgreSQL and then choose the operating system for which you wish to download. Here I will choose Windows.

    PostgreSQL-Tutorial-navigate

    Step 2: Once, the operating system is chosen, you will be redirected to a page, where you have to download the installer. To do that click on the option: Download the installer. Refer below.

    PostgreSQL-Tutorial-navigate

    Step 3: Then, you will be further redirected to a page, where you have to choose the installer version based on the Operating System. Here, I will choose the 11.4 version for Windows 64 bit. Refer below.

    Once, you hit on Download, you will automatically see that PostgreSQL is getting downloaded.

    PostgreSQL-Tutorial-navigate

    Step 4: Now, once the file is downloaded, double click on the file to open it and a wizard will appear on your screen as below. Click on Next and proceed further.

    PostgreSQL-Tutorial-navigate

    Step 4.1: Now, specify the Installation Directory. Here, I will leave it as it is, and click on Next as below.

    PostgreSQL-Tutorial-navigate

    Step 4.2: Now, choose the components which you wish to install and then click on Next. Here, I am selecting all the components.

    PostgreSQL-Tutorial-navigate

    Step 4.3: Next, select the directory where you want to store data. Here I am going to leave it as it is. Then, click on Next.

    PostgreSQL-Tutorial-navigate

    Step 4.4: In the next dialog box, which comes, you have to mention the password for the super user. Then, click on Next.

    PostgreSQL-Tutorial-navigate

    Step 4.5: Next, you have to select the port number on which server should listen. Here, I will let it be as it is and then click on Next.

    PostgreSQL-Tutorial-navigate

    Step 4.6: Finally, select the locale to be used by the new database cluster. I will let it be as it is and then click on Next.

    PostgreSQL-Tutorial-navigate

    Step 4.7: Finally click on Next in the wizards which come to begin the installation of PostgreSQL on your computer.

    PostgreSQL-Tutorial-navigate

    Once, the installation is complete, you will see a dialog box as below on your screen. Click on Finish.

    Postgresql Sample Resumes! Download & Edit, Get Noticed by Top Employers! Download
    PostgreSQL-Tutorial-navigate

    Step 5: Now, you have to connect the server to a database. To do that open pgadmin which is the official GUI of PostgreSQL. Once you open pgadmin, you will see a dialog box, which asks you for the password. So, mention the password, and click on OK.

    PostgreSQL-Tutorial-navigate

    Now that you have installed PostgreSQL, let us get started with commands used in PostgreSQL.

    Are you looking training with Right Jobs?

    Contact Us
    Get Training Quote for Free