Re: PostgreSQL 11 global index

Hi,
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.

Thanks , Mariel.

2018-08-05 23:31 GMT+03:00 Keith <keith(at)keithf4(dot)com>:

>
>
> On Sun, Aug 5, 2018 at 4:58 AM, Mariel Cherkassky <
> mariel(dot)cherkassky(at)gmail(dot)com> wrote:
>
>> Hi,
>> 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.
>
> https://github.com/pgpartman/pg_partman
>
> 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.
>
> Keith
>