Although this answer is bit late but for any other person who is facing same issue this might help. I am not very good with writing so please ignore the mistakes.
There is no straight forward approach or any standard way of adding unique index to partition table without partitioned columns as a part of unique index columns. But we have achieved it with a trick.
You can create an index without include your partitioned column so create an index which you want to be part of unique index.
Then you can create a trigger on your table with Insert operation and on that trigger you can validate if your row is already exist with where clause of your unique filter columns without partitioned column and if it exists then throw error.
Below if the trigger function which I have used.
CREATE OR REPLACE FUNCTION public.my_table_trigger()
RETURNS trigger
LANGUAGE plpgsql
AS $function$
begin
if exists (
select
1
from
my_table_name mts
where
mts.column_1= new.column_1
and mts.column_2 = new.column_2
and mts.column_3 = new.column_3
and mts.column_4 = new.column_4
and mts.column_5 = new.column_5
and mts.column_6 = new.column_6) then
raise exception 'duplicacy error';
else
return new;
end if;
end;
$function$
;
Cons of this approach
This functionality scan every time the table whenever insert query executed.
Impact Minimization
But impact will be minimized as index will be created as step 1 for table scan on every insert statement.
Although this approach is just a method to achieve whatever you want but this can lead to slower insert queries. So please analyse your execution cost on your table before use it for production.
