The solution you suggested arent helpfull (both unique index and pg_partman) because I need to make sure that in all the partitions I have a specific column that is unique. In otherwords one column can have the same value in two different partitions and that is the concept of the global index.
I read the documentation but i didnt find any word regarding global index. I saw a new feature that indexes that exist on the parent automaticly created on the childs but is there any connection between the indexes ?
I'm trying to make sure that 2 different partitions wont have the same data on some of the columns and the partition col isnt one of those column. In oracle that kind of index is called global index.
Do you now some third extension maybe that allow you to use such feature ?
Thanks , Mariel.
This feature is not yet supported in PostgreSQL. In PG11, you can create a unique index, but in order for it to apply to the entire partition set, the column must be part of the partition key. I don't believe the native partitioning feature even allows you to create an unique index on the parent table if the partition key isn't part of it.
I've found some work-arounds for this in pg_partman in the mean time.
To support non-partition key unique columns on native partition sets, I have it use a separate template table where you apply your indexes instead of the parent table. And while it will enforce the uniqueness per child table, it will not enforce it across the entire set. To at least watch for this happening, I've provided a python script that goes through all the child tables and checks for any duplicates across the whole set. So it won't catch it at the time of insertion, but it should at least let you know if/when it happens.
