PostgreSQL & JSON

Had some fun exploring JSON and JSONB features in PostgreSQL recently.

First task was to flatten two tables’ rows into a single field of JSON data. Nice intro to json_agg() and row_to_json().

Where it got real fun was extracting that data back out from JSON to rows & columns with the help of jsonb_path_query().

Numeric column names in PostgreSQL

In the Udemy PostgreSQL Bootcamp : Go From Beginner to Advanced, 60+hours course, there was a lesson where we wrote a function to output some SQL to enable dynamic pivot tables:pass the table_name, vertical_header_column, horizontal_header_column, value_column, and data_type and any table can be “pivoted”. Pretty nifty. But… it was hideously difficult to transcribe the code… Continue reading Numeric column names in PostgreSQL

Deploying React Apps

When deploying React apps on Apache or nginx, in a sub-folder of the root / DocumentRoot, there are some issues to consider. While the deployment guide suggests setting “homepage”: “.” in package.json, others have advised against this. Taking that at face value, I used “homepage”: “/react/react-hooks”, which initially appeared to work. However… static images from… Continue reading Deploying React Apps