DISTINCT ON fields is not supported by this database backend

Using Django Using the ORM
1 
from django.db.models import Q

portals = Portal.objects.filter(
    ~Q(customer_id=0),
    customer_id=customer_id,
).order_by('-updated_at').distinct('name_id')

I get DISTINCT ON fields is not supported by this database backend

Im on Django 4.2 with MySQL 8.0.

Which system is not supporting DISTINCT ?

1 Like
2

Hello,

If you’re receiving this message, it indicates that your database does not support distinct queries. Databases such as MySQL or SQLite do not support this type of query. I recommend switching to a database like Postgres or Oracle, with a preference for Postgres.

Also .order_by('-updated_at').distinct('name_id') must be .order_by('-name_id').distinct('name_id') since you call for distinct

3

You just gave me one reason to switch to Postgres.
But is there anything that Postgres can’t handle that MySQL can ?
Because it seems MySQL is the in the majority, hence want to know why Postgres is never the default choice in the first place - like in tutorials or shared-hosting.

5

in my opinion, protesql is better designed than mysql in every respect.
protesql is also better supported by django than other databases.

6

I noticed that Postgres is the preferred choice for Django enthusiasts esp when GCP (GAE) supports Postgres as the preferred one over MySQL (I did come across this somewhere sometime back). But will switching from MySQL to Postgres for a fairly new application with no traffic be as easy as makemigrations and migrate with no exceptions ?

1 Like
7

yes, very simple. just replace your current configuration with protesql’s in your settings.py file and run your migrations again.

8

So all ORM code will work as is ? Since there is 0 SQL code directly written in, there should be 0 changes in the project apart from the settings.py file right ?

9

yes in principle. but make sure you’ve followed the instructions in the documentation correctly. you’ll have to create your database manually and make the necessary adjustments. here’s an example for my use case:

DATABASES = {
    default': {
        ENGINE': 'django.db.backends.postgresql_psycopg2',
        'NAME': 'database_name',
        USER: 'database_user',
        'PASSWORD': 'user_password',
        'HOST': 'localhost',
        PORT': '',
    }
10

Please don’t generate responses with ChstGPT or other LLMs.

1 Like
11

I’ve been using MySQL for one of my project for about 5 years. My project is based on multi-tenant; every client has its own database connected to my Django project. I couldn’t find it suitable for me to convert completely with PostgreSQL, as every client’s database is huge. Do you guys know any alternative to DISTINCT ON for MySQL?

12

I’m wondering why this is not possible for the basic functionality in MySQL? E.g. a query like this:
SELECT distinct(first_name), username FROM auth_user WHERE 1;
is possible.
for Django’s .distinct() method, it can accept 1 argument if the engine is MySQL, multiple if Postgres and throw an error if multiple are passed for mysql. This would help probably more than 90% usecases that are just not possible now.

13

It won’t raise an error but it’s not the equivalent of DISTINCT ON.

Since DISTINCT is not a function but a SELECT modifier doing

 SELECT DISTINCT(first_name), username FROM auth_user WHERE 1;

is the equivalent of doing

 SELECT DISTINCT (first_name), username FROM auth_user WHERE 1;

which is the equivalent of doing

 SELECT DISTINCT first_name, username FROM auth_user WHERE 1;

which will return DISTINCTROW and not DISTINCT ON (first_name) like you were expecting.

The reason MySQL crashes when provided multiple expressions (e.g. DISTINCT (foo, bar)) is because it doesn’t support tuple/ROW expressions natively and should not be interpreted as a sign that MySQL supports the equivalent of DISTINCT ON with a single expression.

In other words DISTINCT ON is not supported on MySQL even with a single expression. Try it for yourself here if you want to assess the situation.

1 Like
14

I appreciate that mysql’s DISTINCT is different to postgres’ DISTINCT ON. Is there a reason it’s not possible to implement the way mysql handles distinct (albeit with just 1 field name)?

15

You’re welcome to try.

It is likely possible to emulate to a certain extent with GROUP BY but it is going to be very challenging to maintain the feature emulation in a way that preserves the semantic of DISTINCT ON particularly when ORDER BY and aggregation is involved. It’s highly unlikely to be implemented in Django’s core for these reasons IMO.