Before I start the week, let’s wrap up the weekend. I was hacking on HamSql and got in trouble with PostgreSQL arrays again. Recently, I stumbled over misleading documentation for array operators. I wanted to report this issue and remembered that the PostgreSQL community is working without bug trackers. But don’t be scared, I got a really fast and kind reaction on the mailing list and in this way the 9.5 documentation covers those pitfalls explicitly. This weekend I hit the PostgreSQL “array lower bound feature”. The index of PostgreSQL arrays starts at 1, but that’s only a default. You can set the default to any number you like. I did forget this feature immediately after reading the docs and would just have ignored this feature for ever if not PostgreSQL internals would use it some times. It comes as no surprise that many client libraries don’t know how to handle arbitrary lower bounds for arrays. Unfortunately, the postgresql-simple Haskell library is no exception. It took me some time to realize the problem, as the issued error message was not that helpful.
While trying to work around this bug I hit another array function corner case. The documentation states for array concatenation “the result retains the lower bound subscript of the left-hand operand’s outer dimension”. Hence, I should be able to fix the problem using something like '{0}'::int[] || '[0:1]={1,2}'. And this works just fine. So let’s just take an empty array on the left site: '{}'::int[] || '[0:1]={1,2}'. Booom! This acts as identity and leaves the bounds untouched. I am using ARRAY(SELECT UNNEST(...)) to reset the lower bound now. Not sure if I should report this || operator issue too.
