Postgres UPSERT syntax confusion

2
votes

I have a table with 2 columns: channels TEXT rowid INTEGER PRIMARY KEY

I included an index on channels CREATE UNIQUE INDEX channels_index on mytable (lower(channels))

so that VisitToronto will be a conflict with visittoronto

All works well and the conflict fires. ERROR: duplicate key value violates unique constraint "channels_index" DETAIL: Key (lower(words))=(hello world) already exists.

I can not figure out the syntax to trap this conflict. ON CONFLICT channels doesn't work ON CONFLICT ON CONSTRAINT channels_index doesn't work

The closest I've got is: ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification

Any direction would be appreciated.

TIA

1
postgresqlupsert

1 Answers

1
votes

Use the index expression, i.e. lower(channels):

insert into my_table (channels) values
('VisitToronto');

insert into my_table (channels) 
values ('visittoronto')
on conflict (lower(channels)) do 
update set channels = excluded.channels;

select *
from my_table;

 id |   channels   
----+--------------
  1 | visittoronto
(1 row)

You are not able to use a constraint because the index is on an expression. In the case Postgres cannot create a constraint:

alter table my_table add constraint channels_unique unique using index channels_index;

ERROR:  index "channels_index" contains expressions
LINE 1: alter table my_table add constraint channels_unique unique u...
                                 ^
DETAIL:  Cannot create a primary key or unique constraint using such an index.