It fails on the Last line with the following error: SQL Error : ERROR: duplicate key value violates unique constraint "UserFavorites_Partition_2020_pkey"ĭetail: Key ("CreationDate")=( 09:38:54.997) already exists. Insert into "UserFavorites" ("Id", "UserId", "CardId", "CreationDate") select * from "UserFavorites_old" Perform createPartitionIfNotExists(to_date(rec::varchar,'yyyy')) Ĭreate or replace view "UserFavorites" as select * from "UserFavorites_master" execute format('create unique index on %I (%I)', tableName, 'UserId'::text) Every donation helps the project maintain a high level of quality in both code and documentation and donations help the ongoing effort. Help the Ebean project maintain releases and documentation. Unfortunatelly Postgres forces us to define index for each table individually: See the project github releases for details of the released versions. 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) Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW) This function returns the current database system timestamp as a datetime value, without the database time zone offset. 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. When You try to call it for the second time. 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. Jun 27, 2016, 7:57:12 AM to Ebean ORM Hello I think there is a bug in Ebean asOf (timestamp) for historic tables method. but everyone out there has logical models with "timestamp" in them (and those map to timestamptz). Date and time are relevant to pretty much every PostgreSQL application. It might be nice for the logical model to have "timestamptz" and "timestamp local". This is the source of the reason why works in create-all.sql ddl generation but not really via DB migrations. Right now logical "timestamp" pretty much translates to "timestamp with timezone". Now the issue as I currently see it is that ideally we have a distinction between "timestamp" and "timestamp with timezone". We have these "logical types" so that we can generate multi-platform DDL of the same logical model/diff etc. With our DB migrations we have "logical types" and then "platform specific types".įor example we can have logical type like json(1000) which might translate into varchar(1000) on H2 and JSON on PG. FYI: I am still pondering the approach here.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |