A quickpost inspired by issues I encountered recently.

1. How to concatenate (aggregate) strings with PostgreSQL? In MySQL you can write:

   SELECT concat(name) FROM test_table

   Because concat also works as an aggregate function (like MIN, MAX, SUM, COUNT, etc). To get the equivalent result in PostgreSQL, you can use the following query (based on ideas from a previous post):

   SELECT array_to_string(ARRAY(SELECT name FROM test_table), 'separator')

   This is a hack in some ways, because you should write a custom aggregate function, but it works.

2. How to convert date/time to a Unix timestamp with PostgreSQL?

   SELECT date_part('epoch', NOW())

   This returns a floating point number (to represent also the milisecond of the timestamp). If you want just second level precision, you need to convert it to an integer (with the remark that you need consider the issue of rounding up, rounding down, etc):

   SELECT date_part('epoch', NOW())::INTEGER

3. How to find the point of execution (from the point of the callstack) in a Java program and possibly store it for later display? This is useful in asynchronous situations, where objects are created in one place, but their methods called in other places. The solution: create a Throwable object, because it keeps track of the creation point (and no, creating a Throwable object doesn’t cause exceptions, throw-ing them does):

   // where you are interested in the stack
   Throwable callingPoint = new Throwable();
   
   // later on (in a different method / thread / etc)
   callingPoint.printStackTrace(); // this dumps the stacktrace to the stderr
   
   // ...
   
   // or, if you want to make something more sophisticated with the stacktrace
   StringBuilder sb = new StringBuilder();
   sb.append(e.getMessage()); sb.append('n');
   for (StackTraceElement ste : e.getStackTrace()) {
   	sb.append('t'); sb.append(ste); sb.append('n');
   }

Picture taken from selena marie’s photostream with permission.