Postgresql's regexp_split_to_table gotcha

I recently hat to process data from a mal-structuled excel file. Yes, this happens.

As usual, with such situation, the proper answer is: “Do not bother using excel, just feed the data to postgresql and use it to make your data sing”. So I went with it, COPY sometable FROM '/path/to/the/csv/file.csv' WITH CSV, got my data in and I was able to process it. Easy.

At some point, I ended up with a multi-valued columns (really, this should not be usual, this should be banned), with a coma separated list of values. No real difficulty here either, postgresql gives a really usefull regexp_split_to_table.

Basically, it takes a string, split its content on any delimiter that matches the regexpr given in its second argument, and produce one line for each obtained value. Any other column around is just duplicated.

Lest take the following table for example:

We can check the tastes of each person:

tmp=> WITH data(id, username, liquores) as (
  values (1, 'johndo', 'wine, rum'),
         (2, 'janedo', 'whiskey, rum')
) SELECT id, username, regexp_split_to_table(liquores, E',\\s*') FROM data;
 id | username | regexp_split_to_table
----+----------+-----------------------
  1 | johndo   | wine
  1 | johndo   | rum
  2 | janedo   | whiskey
  2 | janedo   | rum
(4 rows)

Or do something more useful and find the most appreciated liquor:

tmp=> WITH data(id, username, liquores) as (
  values (1, 'johndo', 'wine, rum'),
         (2, 'janedo', 'whiskey, rum')
) SELECT regexp_split_to_table(liquores, E',\\s*') as liquor,
         count(*) as cnt
    FROM data
    GROUP BY liquor
    ORDER BY cnt DESC
    LIMIT 1
;
 liquor | cnt
--------+-----
 rum    |   2
(1 row)

This is pretty convenient, something not yet perfect but more usable. The god idea would be to create a properly formalized tables with this (i.e. with a user and a liquor tables). But you know how it works, sometimes you just do not take time to do things the good way and go with the quick and dirty one.

So I went the quick and dirty way and generalized this technique over multiple columns at once where I needed it. And this is wrong. regexp_split_to_table did not do what I expected. I thought it would generate a Cartesian product of all values. It does not.

tmp=> WITH data(col1, col2) as (
  values ('1,2,3,4', '1,2,3')
) SELECT regexp_split_to_table(col1, E',\\s*') as v1,
         regexp_split_to_table(col2, E',\\s*') as v2
    FROM data;
 v1 | v2
----+----
 1  | 1
 2  | 2
 3  | 3
 4  |
(4 rows)

It generates a number of lines equal to the maximum number of values generated by one regexp_split_to_table, the first line gets all the first values generated by the regexp_split_to_table, the second line get the second values and so on. If a rexexp_split_to_table did not yield enough values, null is used.

So be warned: Do not use regexp_split_to_table more than once, except if you actually know what you are doing.

For the record, the way to generate the Cartesian product of all values of col1 with all values of col2, one could use:

tmp=> WITH data(col1, col2) as (
  values ('1,2,3,4', '1,2,3')
), tmp1(v1, col2) as (
  SELECT regexp_split_to_table(col1, E',\\s*') as v1, col2 FROM data
), tmp2(v1, v2) as (
  SELECT v1, regexp_split_to_table(col2, E',\\s*') as v2 FROM tmp1
)
SELECT * FROM tmp2;
 v1 | v2
----+----
 1  | 1
 1  | 2
 1  | 3
 2  | 1
 2  | 2
 2  | 3
 3  | 1
 3  | 2
 3  | 3
 4  | 1
 4  | 2
 4  | 3
(12 rows)