Postgres Syntax to DJANGO ORM Syntax

Question

I have the following Postgres syntax, that does what I want it to do, however, I would like to implement it in the models.py tab in ORM.

SELECT split_part(booking_inform, ',', 1) AS col1
     , split_part(booking_inform, ',', 2) AS col2
     , split_part(booking_inform, ',', 3) AS col3
     , split_part(booking_inform, ',', 4) AS col4
     , split_part(booking_inform, ',', 5) as col5
     , split_part(booking_inform, ',', 6) as col6

FROM   main_inmate;

I am splitting the column "booking inform" into six new columns based on a commas.

Thanks!

Why not store it in six different columns? It will make it harder to update/filter/... if you store it in e column. — willeM_ Van Onsem
– willeM_ Van Onsem, Commented Nov 15, 2020 at 23:21
as Willem already stated it would be better to split it in columns, if you still dont want to do it check docs.djangoproject.com/en/3.1/ref/models/expressions/… — iklinac
– iklinac, Commented Nov 15, 2020 at 23:54

Antoine Pinsard · Accepted Answer · 2020-11-16 01:42:59Z

2

Here is how your Postgresql query would translate in Django ORM. Assuming your model is named MainInmate.

from django.db.models import Func

class SplitPart(Func):
    function = 'split_part'
    arity = 3

MainInmate.objects.annotate(
   **{f'col{i}': SplitPart(F('booking_inform'), ',', i) for i in range(1, 7)}
).values(*[f'col{i}' for i in range(1, 7)])

However, if you store a list of values in a column, I would rather recommend to use a column of array type, which is available as ArrayField in Django.

answered Nov 16, 2020 at 1:42

Antoine Pinsard

35.3k9 gold badges73 silver badges91 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Souvik Ray Over a year ago

Hi Antoine, if I have to also do an average on each of the column post split, what do I need to do additionally? It would be great if you could answer this.

Collectives™ on Stack Overflow

Postgres Syntax to DJANGO ORM Syntax

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related