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