id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
32688	The ON CONFLICT sql suffix creates a syntax error on m2m inserts	Malik A. Rumi	nobody	"When I made the leap from 1.11.5 to 3.1.1, I ran into this issue: Whenever I tried to insert on an m2m related model / field, I got this error:
{{{
ProgrammingError('syntax error at or near ""ON""\nLINE 1: ...ry_tags"" (""entry_id"", ""tag_id"") VALUES (3353, 31) ON CONFLIC...\n 
}}}
I have never seen this error before, and the fact that it is excessively truncated [ one might even say, 'butchered' ], didn't help. The problem was that Django was adding this 'sql suffix' - ""ON CONFLICT, DO NOTHING"", to the end of my insert statement, and then Django turned around and treated it as a syntax error on my inserts. ''But there was **no** conflict!  So the solution was obvious: stop Django from adding this unnecessary and irrelevant suffix.

A lot of digging led me to two files:
/home/malikarumi/.virtualenvs/legacy_insert/lib/python3.8/site-packages/django/db/backends/base/operations.py
/home/malikarumi/.virtualenvs/legacy_insert/lib/python3.8/site-packages/django/db/backends/postgresql/operations.py

In base/operations, the kwarg ''ignore_conflicts'' is set to **False**, while in postgresql/operations.py, it is set to **None**. Was this intentional? Because as I will show you in a moment, the code has examples of this variable taking on [True, False, None, and self.query.ignore_conflicts]. Digging further, I looked at   /home/malikarumi/.virtualenvs/legacy_insert/lib/python3.8/site-packages/django/db/models/sql/subqueries.py, where ignore_conflicts is initially set to False in class InsertQuery(Query), and 
/home/malikarumi/.virtualenvs/legacy_insert/lib/python3.8/site-packages/django/db/models/sql/compiler.py, where it is set to self.query.ignore_conflicts.

I found what I was looking for on lines 1382-3 of compiler.py:
            # if ignore_conflicts_suffix_sql:
            #     result.append(ignore_conflicts_suffix_sql)
and on lines 284-5 of postgresql/operations.py:
    # def ignore_conflicts_suffix_sql(self, ignore_conflicts=None):
    #     return 'ON CONFLICT DO NOTHING' if ignore_conflicts else super().ignore_conflicts_suffix_sql(ignore_conflicts)
 
After commenting them both out, my inserts proceed as normal. So, maybe I'm the only one with this issue. I don't know. I don't know why insert_conflicts seems to be able to take any arbitrary value. Even if it is a nullable Boolean, True, False, and None are not ==, so if False is passed down to a method expecting it to be None, then the alternate condition is going to be triggered. I don't know why this sql suffix was attached to my insert when there was no conflict, causing a syntax error and preventing me from adding new data. 

If you think this is a bug, great. If not, at least I fixed the issue for me. Thanks."	Bug	closed	Database layer (models, ORM)	3.2	Normal	needsinfo		Patrick K	Unreviewed	0	0	0	0	0	0