I am trying to insert data into table. Both source and target have same DDL structure. Source table has Dynamic partitions. Source DDL:
CREATE MULTISET TABLE db1.tb1 ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT,
VPERIOD DATE FORMAT 'YYYY-MM-DD' NOT NULL,
LOADED_TS TIMESTAMP(0) DEFAULT CURRENT_TIMESTAMP(0))
PRIMARY INDEX PI_CUSTICPR ( CUSTID )
PARTITION BY RANGE_N(VPERIOD BETWEEN ADD_MONTHS(((DATE + 1 )- (EXTRACT(DAY FROM (DATE )))),(-36 )) AND DATE - (EXTRACT(DAY FROM (DATE ))) EACH INTERVAL '1' MONTH );
target DDL is exactly same. However i get partition voilation error when I do simple INSERT INTO db1.target SEL * from db1.tb1;
Can anyone help me if there is something wrong with dynamic partition.
Sending a bunch of rows to NO RANGE partition would eliminate the error, but is unlikely to be the desired result.
Note that the definition of partitions based on CURRENT_DATE (what you are calling "dynamic partitioning") is established at CREATE time and is in fact static except at the points where you explicitly change via ALTER TABLE TO CURRENT.