PostgreSQL¶

PostgreSQL or Postgres is a free and open-source relational database management system which is compliant with the Structured Query Language (SQL) standard for storing, manipulating and retrieving data from a relational database. PostgreSQL has cross-platform support for Windows, Mac and Linux.

In PostgreSQL tables can be linked together by joining tables on shared 'keys' which are the columns of values that can be used to identify and select rows from each table. Tables are held within a 'schema' which provides a namespace for different tables the indexes which can be created to improve the performance of database queries by providing convenient means for the system to quickly lookup values in particular columns of each table.

PostgreSQL supports database transactions with ACID properties of atomicity, consistency, isolation and durability, intended to guarantee the validity of data despite errors and issues like power failures. Other features provided by PostgreSQL databses include updatable views, materialized views, triggers, foreign keys, and stored procedures.

Personal Experience¶

For the large part my use of PostgreSQL has been as a means to setup test APIs by deploying a database with Docker and using PostgREST combined with Swagger to generate a self-documenting API. In the past I've used pgAdmin or DBeaver to monitor and query the database. Often I use FME to transfer larger amounts of data in and out of the database. I also use QGIS to view and edit data when working with geographic information via the PostGIS extension. More recently I've begun using psycopg with Python in Visual Studio Code to analyse the database contents. I've also used Apache Superset to visualise the data in dashboards which are more readily consumed by non-specialists.

Tools & Software Integrations¶

Apache Superset - A free and open-source business intelligence (BI) and dashboarding and data visualisation platform that integrates with a range of SQL databases and utilises SQL as its query language.
FME - Safe Software's feature manipulation engine (FME) is an ETL tool which extract, load and manipulate data from a range of sources including PostgreSQL databases. FME also lets you perform SQL queries both before and after extraction and loading.
pgAdmin - A free and open-source database administration tool for PostgreSQL with cross-platform support for Windows, Mac and Linux.
Psycopg - A PostgreSQL adaptor for interacting with and querying Postgres databases using the Python programming language.
QGIS - QGIS is a free and open-source (FOSS) geographic information system (GIS) with cross-platform support for Windows, Mac and Linux. QGIS can connect to PostGIS and provides a means for viewing the databases content in context on a basemap and maniplating and updating the data via the QGIS grapgical user interface (GUI).
Supabase - A cloud-based service offering a hosted PostgreSQL database, web-based GUI for database management, instant APIs, real-time subscriptions and additional storage. They currently provide a free tier for databases up to 500mb.

Recommended Plugins¶

pgvector - An open-source PostgreSQL extension that enables you to store vector embeddings form machine learning applications and perform vector similarity search. It supports both exact and approximate nearest neighbour searches along with L2 distance, inner product, and cosine distance measures of similarity.
PostGIS - This PostgreSQL extension adds support for processing geographic information. This enables Postgres to be used as a backend spatial database for geographic information systems like QGIS. Specifically PostGIS adds support for working with vector and raster datasets including ESRI shapefile, KML, GML, GeoJSON, GeoHash, WKT, GeoTIFF, PNG, JPG and NetCDF. PostGIS also provides functions indexing, processing and reprojectining those data. There is also support for working with 3D objects and network topology.
PostgREST - A standalone web server that converts a Postgres database into a RESTful API using the databses constraints and permissions to generate the API endpoints and specify their operations.

Resources¶

PostgreSQL Website: https://www.postgresql.org/
PostgreSQL Tutorial Website: https://www.postgresqltutorial.com/
W3Schools SQL Tutorials: https://www.w3schools.com/sql/

Notes and Troubleshooting¶

Change Column Data Type¶

ALTER TABLE table_name ALTER COLUMN column_name TYPE new_data_type;

NOTE: If Postgres returns an error you may need to cast the data type explicitly with a USING statement as hinted in the error:

ERROR: column "colum_name" cannot be cast automatically to type integer HINT: You might need to specify "USING column-name::new_data_type".

Example:

ALTER TABLE assets ALTER COLUMN column_name TYPE INT USING column_name::integer;

Source: PostgreSQL Change Column Type: Step-by-Step Examples

Find null values in a table or column¶

Find all rows containing null values in a tables:

SELECT *
FROM table_name
WHERE NOT (table_name IS NOT null);

Find all rows containing null values in a particular column:

SELECT *
FROM table_name
WHERE NOT (table_name.column_name IS NOT null);

Source: How to check for null values in a row?

Find tables by column name¶

The following query will find a tables with a specific column name specified in the statement WHERE c.column = 'column_name':

SELECT
    t.table_schema,
    t.table_name,
    c.column_name,
    c.data_type
FROM information_schema.tables t
INNER JOIN information_schema.columns c ON c.table_name = t.table_name
    AND c.table_schema = t.table_schema
WHERE c.column_name = 'column_name'
    AND t.table_schema NOT IN ('information_schema', 'pg_catalog')
    AND t.table_type = 'BASE TABLE'
ORDER BY t.table_schema;

You can also perform a fuzzy column name search which is not case sensitive by using a wilcard operator % with a LIKE query and coercing the search term and it's comparator to lower case using the lower() function:

SELECT 
    t.table_schema,
    t.table_name,
    c.column_name,
    c.data_type
FROM information_schema.tables t
INNER JOIN information_schema.columns c ON c.table_name = t.table_name
    AND c.table_schema = t.table_schema
WHERE LOWER(c.column_name) LIKE LOWER('%column_name%')
    AND t.table_schema NOT IN ('information_schema', 'pg_catalog')
    AND t.table_type = 'BASE TABLE'
ORDER BY t.table_schema;

Source: Find tables with specific column name in PostgreSQL database

List Columns for a Table¶

SELECT   
    table_name,   
    column_name,   
    data_type,  
    is_nullable  
FROM   
    information_schema.columns  
WHERE  
    table_schema = 'schema_name'  
    AND table_name = 'table_name';

Source: List all columns for a specified table

List Indexes for all tables within a particular Schema¶

SELECT tablename,
    indexname,
    indexdef 
FROM
    pg_indexes
WHERE
    schemaname = 'public'
ORDER BY
    tablename,
    indexname;

Source: PostgreSQL List Indexes

List Indexes for a particular Table¶

SELECT
    indexname,
    indexdef
FROM
    pg_indexes
WHERE
    tablename = 'table_name';