Hi, is there any chance (risk ?) that a given gid be present in more than one region ? if not (or if you implement it via a dedicated, non partition table),
you may create a simple table partitioned by region, and create unique indexes for each partition. this is NOT equivalent to a unique constraint at global table level, of course. Marc MILLAS Senior Architect +33607850334 www.mokadb.com On Tue, Mar 1, 2022 at 4:37 PM Andrew Zakharov <andrew...@mail.ru> wrote: > Hello all – > > I have a task which is simple at the first look. I have a table which > contains hierarchy of address objects starting with macro region end ends > with particular buildings. You can imagine how big is it. > > Here is short sample of table declaration: > > > > create table region_hierarchy( > > gid uuid not null default uuid_generate_v1mc(), > > parent_gid uuid null, > > region_code int2, > > … > > constraint pk_region_hierarchy primary key (gid), > > constraint fk_region_hierarchy_region_hierarchy_parent foreign key > (parent_gid) references region_hierarchy(gid) > > ); > > > > Being an Oracle specialist, I planned to using same declarative > partitioning by list on the region_code field as I did in Oracle database. > I’ve carefully looked thru docs/faqs/google/communities and found out that > I must include “gid” field into partition key because a primary key field. > Thus partition method “by list” is not appropriate method in this case and > “by range” either. What I have left from partition methods? Hash? How can I > create partitions by gid & region_code by hash? Feasible? Will it be > working properly (with partition pruning) when search criteria is by > region_code only? Same problem appears when there is simple serial “id” > used as primary identifier. Removing all constraints is not considered. I > understand that such specific PostgreSQL partitioning implementation has > done by tons of reasons but how I can implement partitioning for my EASY > case? I see the only legacy inheritance is left, right? Very sad if it’s > true. > > Your advices are very important. > > Thanks in advance. > > Andrew. > > >
