Linux pros and enthusiasts
Content related to databases and SQL
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().
The topic of JOINs was thoroughly covered (JOINs are old news), but one user (Mani) made this comment: I have gone through videos, but lateral join concept is missing. Please update course with lateral join and use case. This prompted a digression into LATERAL JOINs as it was a new thing to me. My reply… Continue reading LATERAL JOINs in PostgreSQL
\gexec in PostgreSQL is handy when running a function that outputs dynamically generated SQL to be run to get final results
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
PostgreSQL Pivot Tables using \crosstabview with sortation based upon various criteria