In one of our use cases where we are importing form submissions from the KoboToolbox API into a PostgreSQL database, we are running into an issue with lengthy column headers. PostgreSQL imposes a maximum identifier length of 63 characters for column names, and sometimes the Kobo form question names can be much longer than that when they are found in a group (or several).
For example, this question column header is located within two groups, and consists of 95 characters in total:
This came from a form that was designed using the KoboToolbox formbuilder, and the user may or may not have adapted the XML headers, and could have shortened them. But we would like to impose a column header rewrite to ensure that it can be imported as a last-ditch effort.
Our current thinking is to reverse the order and make it
question/group/group, and truncate anything >63 by stripping the last 4 characters and adding
_001 and so forth. But this could get tricky if the receiving database (and the code used to handle imports) has no knowledge of the entire Kobo form, and only whatever submissions are sent in, meaning we currently have no way to consistently assign the same numeric suffix to the same question repeatedly. (Although there are workarounds like creating a table in the database that keeps track of the question renames as they come in.)
I’m curious if others have had this same issue, and what kinds of solutions folks may have implemented.