Today I had to get the ISO year start and the ISO year end for a given date. For instance, according to ISO8601 the week number for January 30, 2020 is 5: Year Limits By Date In Year Let’s say that we want to determine the first date of the Read more…
One thing that you may need to use from time to time in Postgres is a function to be able to extract the value of a URL parameter from URLs stored in your DB. I wanted to be able to do this too so I wrote the following function: If Read more…
I recently wrote a post about how to query hierarchical data without recursion in PostgreSQL. Now it is time to talk about how to do it with recursion. 😎 CTEs (Common Table Expressions), AKA WITH clauses, provide us the ability to not only use a query as if it were Read more…
Hierarchical data stored in a relational database such as PostgreSQL can be tricky to work with. An organization is an example of a hierarchical structure. For example, let’s say that we need to represent employees: Davy Crocket Cindy Cruz George Jefferson Blinky Bill Gustavo Frederickson Joan Smith Debora Ferreiro Harry Read more…
One thing I started doing last week was writing PL/pgSQL functions for each of my JasperReports. Each function essentially just runs and returns the rows of a query. One question that I had for a long time was how can I return these results from a function without looping through Read more…
Most Recent Week Day Recently I was reviewing someone’s code for a function in PostgreSQL and I saw a block that was supposed to calculate the most recent Friday of the week. After working on improving it I came up with the following queries that will show the most recent Read more…
Someone asked me to quickly write them some code in any language that would accomplish a certain task. The following, which is in PostgreSQL, is the second solution that I came up with after doing the first one in my favorite language, JavaScript: The question is, what does this SELECT Read more…
The following is one answer to this Problem of the Week that works in PostgreSQL (and probably in other flavors of SQL):
Something I had to do today was figure out how to find the SUM of an array in PostgreSQL. I went through many different ways, but this seemed to be the best: If you know of a shorter or better way to do this for a variable size array please Read more…
Yet another request came to me today for granting SELECT permissions. This time, the request was to give the user the correct privileges to use a view. Basically, you can use the same format that is used for tables as is used for views: After running a query like this Read more…