#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
-------------------------------------+-------------------------------------
Changes (by Simon Charette):
* cc: Simon Charette (added)
Comment:
> 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.
> 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].
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`.
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/34955#comment:11>
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/0107018bb4b8ba85-6ca77517-716b-4b75-96e2-49a4076fa2c1-000000%40eu-central-1.amazonses.com.
