“Jesus answered them, ‘Do you now believe? Indeed the hour is coming, yes, has now come, that you will be scattered, each to his own, and will leave Me alone. And yet I am not alone, because the Father is with Me. These things I have spoken to you, that in Me you may have peace. In the world you will have tribulation; but be of good cheer, I have overcome the world.’ “
Purpose of Post: To provide an overview of SQL, PostgreSQL & pgAdmin.
Main Source:
(#1): PostgreSQL Documentation
(#2): Practical SQL (2nd Ed) by Anthony DeBarros. ISBN-13: 978-1-7185-0107-2 (ebook).
SQL
SQL stands for Structured Query Language & is a language that helps manage data & database systems. The use of SQL allows for greater limits than Microsoft Excel & Access.
SQL is also utilized alongside languages such as R & Python in the data-analysis field.
PostgreSQL 101
PostgreSQL is a free open-source variant of SQL that has existed for over thirty-five years with origins dating back to 1986.
Technically, PostgreSQL is “an object-relational database management system based on POSTGRES” and was developed by the Computer Science Department at Berkeley.
Professor Michael Stonebraker, Andrew Yu & Jolly Chen all played important roles in the historical timeline of PostgreSQL.
PostgreSQL was the new name chosen “to reflect the relationship between the original POSTGRES and the more recent versions with SQL capability [Postgres95].”
Postgres is the widely utilized nickname for PostgreSQL.
As of this post, PostgreSQL is on version 17.2.
The versions started at 6.0, which build upon the version numbering system implemented in the original POSTGRE project.
PostgreSQL runs on all the major operating systems & is ACID-compliant.
As of version 17.2, PostgreSQL license “can be used, modified, and distributed by anyone free of charge for any purpose, be it private, commercial, or academic.”
There is also a PostGIS geospatial database extender. PostGIS allows for the analysis of geometric data.
PostgreSQL is also utilized with cloud computing environments like AWS & Google Cloud. PostgreSQL is highly scalable in quantity of data & concurrent users. In some instances, there are environments that manage both terabytes & petabytes of data.
PostgreSQL offers: complex queries, foreign keys, triggers, updatable views, transactional integrity, multiversion concurrency control, the creation of user data types/ functions / operators / aggregate functions / index methods.
pgAdmin is an open-source graphical administrative tool to be utilized with PostgreSQL database & servers. pgAdmin’s GUI allows you to manage settings, import/export, & write queries.
As of 12-12-2024, pgAdmin 4 v8.14 has been released.
StackBuilder
StackBuilder is “a package manager for downloading & installing additional PostgreSQL tools and drivers. Stackbuilder includes management, integration, migration, replication, geospatial, connectors and other tools.”
Stack Builder version 4.2.2 packages the following:
(1) pgAgent – a job scheduling agent capable of running multi-step batch/shell & SQL tasks on complex schedules.
(2) pgBouncer – a lightweight connection pooler.
(3) Npgsql v3.2.6-3 = a .NET data provider (database driver)
(4) pgJDBC v42.7.2-1 = a collection of JDBC drivers (3, 4, and 41).
(5) psqlODBC 32 & 64 bit v.13.02.0000-1 = official PostgreSQL ODBC driver.
(6) Various versions of the PostgreSQL database server with pgAdmin & Stackbuilder.
(7) Several registration-required EnterpriseDB Tools that will not be discussed here.
(8) PostGIS 3.5 Bundle for PostgreSQL 17 (64 bit) v3.5.0 = PostGIS 3.5.0 bundle includes PostGIS 3.5.0 w GDAL 3.9.2 (SQLite 3.30.1, OpenJPEG 2.4.0, Expat 2.4.8, FreeXL 1.0.6, Arrow 13.0.0) , GEOS 3.13.0, Proj 8.2.1, pgRouting 3.6.3, osm2pgrouting 2.3.8, ogr_fdw 1.1.5 spatial foreign data wrapper extension, and pgPointcloud 1.2.5, h3-pg 4.1.3, MobilityDb 1.2.0.
PostGIS Website link here …
(9) PEM-HTTPD v.2.4.62-1 = a preconfigured Apache webserver, compiled to be utilized with PostgreSQL.
*** The PostgreSQL 17 version no longer packages the Language Pack for Python into the Stack Builder utility. ***
Here are some helpful tips on installing support for Python in PostgreSQL.
“Jesus answered them, ‘Do you now believe? Indeed the hour is coming, yes, has now come, that you will be scattered, each to his own, and will leave Me alone. And yet I am not alone, because the Father is with Me. These things I have spoken to you, that in Me you may have peace. In the world you will have tribulation; but be of good cheer, I have overcome the world.’ “
Leave a Reply