Today, I was asked how to select each element in an array as a separate row. In other words, I needed to convert an array into rows (one for each array element). Below is the right way to do it by using the built-in unnest() function (requires PostgreSQL 8.4+): If Read more…
Recently, since the DBA wasn’t available, I had to assume his duties and grant SELECT privileges to a user (let’s say john_doe) for all of the tables in the public schema. After quite a bit of research (Google of course), I learned about the pg_tables view. After realizing that this Read more…
Yesterday, I was asked if a function exists in PostgreSQL that will accept the year and the week number and will return a day in that week. Unfortunately, I was not able to find one, but perhaps one natively exists (HINT: let me know if one exists by commenting ;)). Read more…
Recently, I had to create a PostgreSQL equivalent of the Java hashCode() function. In order to do this, I needed a way to make sure that numbers that exceed the INTEGER representation (ranging from -2^31 to 2^31-1) will be wrapped. In JavaScript, this is as simple as using the logical Read more…
One of the things that I had to do recently was develop an example iReport which would exemplify how to achieve optional variables solely through the Java code within the JRXML file. My example consisted of creating a query that could serve as an example table named people which contained Read more…
Let’s say that we have a table of grades that students received during a school year. The columns are as follows: student_id quarter1 quarter2 quarter3 quarter4 If you had to display the highest grade that a student received for a quarter, how would you go about selecting the data? Your Read more…
One thing that I often have to do is convert a subquery into a string that is delimited by a comma. For example, I may need to write a quick report that shows the users’ names, their login ID, and all of the roles that they fulfill. In this case, Read more…
Working Solution – Custom Function I have to say that using PostgreSQL has been great compared to MySQL. Recently, though, I ran into an issue while working with iReports designer. I needed to be able to accept a string of comma-delimited integers and then JOIN on those ID’s. Therefore, I Read more…