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?