Issue: Postgres ON CONFLICT for a named index
Author: Nelluk
State: closed
Created: 2018-09-27 11:57:31

I have a table Player with a unique index on two columns. I am trying to do an UPSERT with this index as the ON CONFLICT target.

```python
class Player(BaseModel):
    discord_member = ForeignKeyField(DiscordMember, unique=False, null=False)
    guild_id = BitField(unique=False, null=False)
    nick = TextField(unique=False, null=True)
    team = ForeignKeyField(Team, null=True, backref='player')


    def upsert(discord_member_obj, guild_id, team=None):
        discord_member, _ = DiscordMember.get_or_create(discord_id=discord_member_obj.id, defaults={'name': discord_member_obj.name})

        player = Player.insert(discord_member=discord_member, guild_id=guild_id, nick=discord_member_obj.nick, team=team).on_conflict(
            # conflict_target=[Player.discord_member, Player.guild_id],
            # conflict_target=[idx],
            conflict_target=['playerindex'],
            # conflict_target=['player.playerindex'],
            preserve=[Player.team]
        ).execute()
        return player

    # class Meta:
    #     indexes = ((('discord_member', 'guild_id'), True),)   # Trailing comma is required


idx = Player.index(Player.discord_member, Player.guild_id, unique=True, name='playerindex')
Player.add_index(idx)
```


Initially I was using the easier Meta index, but could not find a way to refer to it in the `conflict_target`. Then I moved on to more advanced index creation and assigned a name to the index, but I cannot find a way to refer to the name. Above are a few methods I've tried commented out. 

I've confirmed with psql that the index exists:
```
Indexes:
    "player_pkey" PRIMARY KEY, btree (id)
    "playerindex" UNIQUE, btree (discord_member_id, guild_id)
```

My guess is that postgres wants the index referred to without quotation marks perhaps, but peewee is inserting them?


`peewee.ProgrammingError: column "playerindex" does not exist`
`LINE 1: ...478571892832206869, NULL, NULL, 1000) ON CONFLICT ("playerin..`


I have no clue why that is, because Postgres will infer the correct unique index if the columns are provided.

For example:

```python

class KV(Model):
    key = TextField()
    value = TextField()
    extra = TextField(default='')
    class Meta:
        database = db
        indexes = ((('key', 'value'), True),)

KV.create(key='k1', value='v1')
KV.create(key='k2', value='v2')

(KV
 .insert(key='k1', value='v1', extra='e1')
 .on_conflict(conflict_target=[KV.key, KV.value], preserve=[KV.extra])
 .execute())

kv = KV.get(KV.key == 'k1')
print(kv.__data__)
# {'id': 1, 'key': 'k1', 'value': 'v1', 'extra': 'e1'}
```

Looking at the schema, I see:

```
# \d kv
                            Table "public.kv"
 Column |  Type   | Collation | Nullable |            Default             
--------+---------+-----------+----------+--------------------------------
 id     | integer |           | not null | nextval('kv_id_seq'::regclass)
 key    | text    |           | not null | 
 value  | text    |           | not null | 
 extra  | text    |           | not null | 
Indexes:
    "kv_pkey" PRIMARY KEY, btree (id)
    "kv_key_value" UNIQUE, btree (key, value)
```

However, if I execute the following in the postgres shell, I get an error:

```sql
insert into kv (key, value, extra) values ('k2', 'v2', 'e2') 
  on conflict ("kv_key_value") do update set extra=EXCLUDED.extra 
  returning id;
ERROR:  column "kv_key_value" does not exist
```

Even creating a named constraint doesn't seem to work:

```sql
create table kv (
  id serial not null, 
  key text not null, 
  value text not null, 
  extra text not null default '', 
  constraint kv_key_value unique(key, value));
```

Which produces slightly different output when describing:

```
\d kv
                            Table "public.kv"
 Column |  Type   | Collation | Nullable |            Default             
--------+---------+-----------+----------+--------------------------------
 id     | integer |           | not null | nextval('kv_id_seq'::regclass)
 key    | text    |           | not null | 
 value  | text    |           | not null | 
 extra  | text    |           | not null | ''::text
Indexes:
    "kv_key_value" UNIQUE CONSTRAINT, btree (key, value) -- NOTE the "CONSTRAINT" bit.
```

However this fails in the same way, with the "column ... does not exist" error. I'm not sure why this is. I've created a question on StackOverflow with all the info from this comment:

https://stackoverflow.com/questions/52542845/postgresql-on-conflict-with-multi-column-unique-constraint-name

Note the postgres docs say:

> It is often preferable to use unique index inference rather than naming a constraint directly using ON CONFLICT ON CONSTRAINT constraint_name.

But I still don't get why the constraint name is not working. Weird.

Thanks to Tom L. from the Postgres mailing list and Laurenz A. on stackoverflow for clarifying the problem. When referencing a constraint, the syntax is:

```sql
ON CONFLICT ON CONSTRAINT constraint_name
```

So I'll need to update Peewee to include the "ON CONSTRAINT" bit where appropriate.

Pushed a fix, but please note that you cannot specify the name of a UNIQUE index (for whatever reason). The constraint name must be the name of a table constraint, which you can create like so:

```python
class KV(Model):
    key = TextField()
    value = TextField()

    class Meta:
        constraints = [SQL('constraint kv_key_value unique(key, value)')]
```

Also note that to specify the name, you will use `.on_conflict(conflict_constraint='kv_key_value')` rather than `conflict_target=...`, which is now reserved for index expressions.