execute format('create unique index on %I (%I)', tableName, 'UserId'::text) Unfortunatelly Postgres forces us to define index for each table individually:
#POSTGRESQL PARTITION BY FULL#
You can see the full code below: alter table "UserFavorites" rename to "UserFavorites_old" Ĭreate or replace function createPartitionIfNotExists(forDate timestamp) returns voidĭeclare yearStart date := date_trunc('year', forDate) ĭeclare yearEndExclusive date := yearStart + interval '1 year' ĭeclare tableName text := 'UserFavorites_Partition_' || to_char(forDate, 'YYYY') Įxecute format('create table %I partition of "UserFavorites_master" for values from (%L) to (%L)', tableName, yearStart, yearEndExclusive) Looking for alternatives or good ideas to solve the issue. The original table didn't have a constraint on timestamp to either be unique or a primary key nor would we particularly want that but that seems to be a requirement of partitioning. "Id" int4 NOT NULL GENERATED BY DEFAULT AS IDENTITY,ĬONSTRAINT "PK_UserFavorites_CreationDate" PRIMARY KEY ("CreationDate") You can see the new table definition below: CREATE TABLE "UserFavorites_master" ( The partition forces me to create the primary to be the range (timestamp) value.
I have an issue when trying to modify and existing PostgreSQL (version 13.3) table to support partitioning it gets stuck when inserting the new data from the old table because the inserted timestamp in some cases may not be unique, so it fails on execution.
0 kommentar(er)
