Category: Database

Adding Custom Delimiters to a String with Regex, PostgreSQL and PL/pgSQL

On this post I will be covering how to use regex to add custom delimiters to a string. The code referenced in this post was written to work in Postgres 10.3. The code will not work as expected in Postgres 9.6 or below. The function that I created as an example for this post takes…


Dynamically Update Multiple Rows with PostgreSQL and Python

On this post I’m going to be covering a neat trick I learned to dynamically update multiple rows in a table with a single query. For this example I have a table called test_table and the structure looks like this: For this post I used python for the application login and pscyopg2 as the postgreSQL client. The entire script is…


CTEs, Recursion, and DFS in postgreSQL

While searching for the best way to write some backend methods I stumbled on an amazing, yet not so new feature of PostgreSQL . Since version 8.4 postgreSQL has supported Common Table Expressions (CTE) and recursion, which is useful for creating complex yet readable queries of hierarchical data. Straight from the postgreSQL docs: “Common Table Expressions or CTEs,…