#34955: Make available the string concatenation operator `||` for PostgreSQL
-------------------------------------+-------------------------------------
Reporter: Paolo Melchiorre | Owner: nobody
Type: New feature | Status: new
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:
Keywords: field, database, | Triage Stage:
generated, output_field | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Paolo Melchiorre):
Replying to [comment:11 Simon Charette]:
> > Unfortunately we can't change Concat() to use `||` because on pg it
_does_ evaluate NULLs. If it were to use `||` then we _would_ need to make
use of Coalesce() to make it consistent.
>
> That's the crux of the issue and why we had to go back and forth on how
it was implemented on Postgres when adding support for functional index
and constraints. There are few tickets related to this such one such as
#29582 and #30385.
Thanks, for pointing us to these old issues.
> > the migrations generate this SQL code (code snipped with many
unnecessary `::text`)
>
> This is a side effect of 09ffc5c1212d4ced58b708cbbf3dfbfb77b782ca
(#33308) to accommodate support for `psycopg>3` server-side bindings. See
779cd28acb1f7eb06f629c0ea4ded99b5ebb670a (#34840) which removed many of
them but
[https://github.com/django/django/blob/f7389c4b07ceeb036436e065898e411b247bca78/django/db/models/functions/text.py#L90
some still remain].
Are you suggesting opening an issue to remove all the remaining
unnecessary `CAST`?
> Based on #30385 I think that the best way forward here is to stop using
`CONCAT` entirely and use a strategy where it relies on `||` with
`Coalesce` and `Cast` appropriately when dealing with expressions that are
nullable and/or non-text. The challenge here is that we can't trust
`.null` as the our output field resolving strategy doesn't carry `null`
affinity. If we want to make sure `Concat` maintains is previous behaviour
on Postgres we ''must'' wrap every source expression in `Coalesce`.
I understand that `GeneratedField` is only the last one affected by the
fact that `CONCAT` is not `IMMUTABLE`
I agree with the plan to replace `CONCAT` everywhere with `||` given how
many problems it would solve.
> Normally when we change the SQL generated by an expression it leaves all
index generated with the previous implementation unusable and forces users
to re-create them but in this case it wasn't possible to even create such
index as `Concat` is not `IMMUTABLE` so I don't think that's an issue.
Better this way I would say, there will be no indexes that the user needs
to re-create.
--
Ticket URL: <https://code.djangoproject.com/ticket/34955#comment:12>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/0107018bb4efd5fe-ec10a6cd-6349-4282-a42e-74c9d4a9515b-000000%40eu-central-1.amazonses.com.
