[Django]-Django. PostgreSQL. regexp_split_to_table not working

by

4👍

✅

The backslash is treated as meta-character by Django and is interpreted inside double quotes.
So one layer of E'\\s+') gets stripped before the string arrives at the PostgreSQL server, which will see E'\s+'). The escape string will result in 's+' which in turn will make regexp_split_to_table() split your strings at any number of s instead of non-printing space, which the character class shorthand \s stands for in regular expressions.

Double your backslashes in the string to get what you intended: E'\\\\s+'):

"SELECT regexp_split_to_table(field_name, E'\\\\s+') FROM mytable LIMIT 20"

As an alternative, to avoid problems with the special meaning of the backslash \, you can use [[:space:]] to denote the same character class:

"SELECT regexp_split_to_table(field_name, '[[:space:]]+') FROM mytable LIMIT 20"

Details in the chapter “Pattern Matching” in the manual.

👤Erwin Brandstetter

2👍

Thanks to new in Django F, Func, and supprot for postgresql ArrayField you can now call this function like this:

from django.db.models import F, Value, TextField
from django.contrib.postgres.fields import ArrayField
from django.db.models.expressions import Func

MyTable.objects.annotate(
  some_field_splitted=Func(
      F('some_field'), 
      Value(","), 
      function='regexp_split_to_array',
      output_field=ArrayField(TextField())
   )
).filter(some_field_splitted__contains=[HERE_SOME_VALUE])
👤andilabs

Leave a comment