#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:4 Mariusz Felisiak]:
> ... I understand that you want to use new goodies on PostgreSQL ...
This isn't about **me** wanting to play with the latest Django features
with PostgreSQL, I solved the problem in my code some time ago by writing
a function that uses `||` instead of `CONCAT`.
The problem, however, remains non-expert users to whom we reiterate that
the generated fields are compatible with all supported databases, and then
the first example we do, uses strings concatenations, that we already know
will not work with at least one of these databases out-of-the-box.
> Moreover, there is no easy way to implement `Concat()` on PostgreSQL
such that it uses ` || `, is immutable, and work the same way as now,
because it uses `COALESCE()` which is also considered mutable by
PostgreSQL ... it's a database limitation, not a Django fault.
On the contrary, in PostgreSQL, you can concatenate strings into a
generated column without any problem.
See for example:
{{{
#!sql
CREATE TABLE "samples_fullnames" (
"id" bigint NOT NULL PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
"first_name" varchar(150) NOT NULL,
"last_name" varchar(150) NOT NULL,
"full_name" text GENERATED ALWAYS AS (
"first_name"::text || ' '::text || "last_name"::text
) STORED
);
}}}
But also: ''(mimicking the SQL code generated by the ORM for SQLite)''
{{{
#!sql
CREATE TABLE "samples_names" (
"id" bigint NOT NULL PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
"first_name" varchar(150) NOT NULL,
"last_name" varchar(150) NOT NULL,
"full_name" text GENERATED ALWAYS AS (
COALESCE(("first_name")::text, '') || COALESCE(COALESCE(('
')::text, '') || COALESCE(("last_name")::text, ''), '')
) STORED
);
}}}
If there is a limitation I would say it lies in how the Django ORM
generates the SQL code for PostgreSQL.
{{{
#!sql
CREATE TABLE "samples_user" (
"id" bigint NOT NULL PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
"first_name" varchar(150) NOT NULL,
"last_name" varchar(150) NOT NULL,
"full_name" text GENERATED ALWAYS AS (
CONCAT(("first_name")::text, (CONCAT((' ')::text,
("last_name")::text))::text)
) STORED
);
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/34955#comment:5>
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/0107018baf4a4361-53a78e4d-2b33-46ad-a417-d5b928883df5-000000%40eu-central-1.amazonses.com.
