Yada Yada Yada

string_to_array behaviour with an empty string

So this caught me out the other day. We had a query which broke out multiple delimited strings each with one or more elements (but always the same number of elements in each) into multiple records using string_to_array and unnest. In each string any element could potentially have an empty value, but at least one string would always have values for all the elements. This meant there should always be at least one output row. So below worked as expected:

But then we discovered that there was some data that was being completely lost in the query output. Turns out that this occurred when, like above there was just one element in the input, but one value was empty:

So after some investigation it turns out that this happens because string_to_array on an empty string returns an array with no elements rather than an array with one element which is an empty string. This does actually make sense because without any application specific context it is indeterminate whether an empty string should be treated as having no items or should itself be treated as one item. There is a very full and complete discussion of the reasoning behind reaching the decision to return an empty array:

We are using PostgreSQL version 9.5 and we knew an upper bound on the number of elements so we could get away with doing [2:100] but in PostgreSQL version 9.6 you can now do [2:] to leave the upper bound unspecified and defaulting to the array length which is obviously nicer!

The behaviour of string_to_array on a zero length string is of course documented on https://www.postgresql.org/docs/9.5/static/functions-array.html but in a rather oblique way under a supplementary note documenting changes in behaviour from before PostgreSQL version 9.1 when NULL was returned instead of an empty array.

The University of Edinburgh is a charitable body, registered in Scotland, with registration number
SC005336, VAT Registration Number GB 592 9507 00, and is acknowledged by the UK authorities as a
“Recognised body” which has been
granted degree awarding powers.

Academic Blogging Service provided by the University of Edinburgh. Get your own blog.

Report this page

To report inappropriate content on this page, please use the form below. Upon receiving your report, we will be in touch as per the Take Down Policy of the service.

Please note that personal data collected through this form is used and stored for the purposes of processing this report and communication with you.

If you are unable to report a concern about content via this form please contact the Service Owner.

Your nameYour email addressPlease enter an email address you wish to be contacted on.Report descriptionPlease describe the unacceptable content in sufficient detail to allow us to locate it, and why you consider it to be unacceptable.By submitting this report, you accept that it is accurate and that fraudulent or nuisance complaints may result in action by the University.