partition_generated.sql

create table tbl_main(

item_id int GENERATED ALWAYS AS IDENTITY,
-----------------------------------------------------
operating_offices int [] GENERATED ALWAYS AS (
nullif(array[(info->>'o')::int], '{NULL}') ) stored ,
-----------------------------------------------------
primary_bill_to_id int   GENERATED ALWAYS as ((info->>'vp')::int) stored ,
----------------------------------------------
item_status_array text [] GENERATED ALWAYS as ( array[
coalesce(info->>'qr', info->>'s'), info->>'v'] ) stored ,
-------------------------------------------------
info jsonb
------------------------------
,is_complete bool  GENERATED ALWAYS as (coalesce( (info->>'lf')::bool =
true or (info->>'lg')::bool = true, false)) stored
--------------------------------------------
,is_deleted bool GENERATED ALWAYS as ( coalesce( (info->>'cv')::bool,
false) ) stored
------------------------------
,is_a_template bool GENERATED ALWAYS as ( coalesce( (info->>'cw')::bool,
false) ) stored
-------------------------------------------
,created_by_user_id int
,created_on timestamptz default now()
----------------------------------
,primary key(item_id,created_on )


) partition by range (created_on) ;

---=================================================================
-- *** index

CREATE INDEX tbl_main_idxgin ON tbl_main USING gin (info);


---=================================================================
 -- **** partitions

-- default partition
create table tbl_main_partition_default
partition of tbl_main default;

create table tbl_main_partition_2021
partition of tbl_main
for values from ('2020-01-01') to ('2022-01-01');

create table tbl_main_partition_2022
partition of tbl_main
for values from ('2022-01-01') to ('2023-01-01');

create table tbl_main_partition_2023
partition of tbl_main
for values from ('2023-01-01') to ('2024-01-01');

insert into tbl_main (info, created_on) values
 ('{"vp": 42, "o": 1000, "qr": "foo"}', '2023-06-01'),
 ('{"vp": 43, "o": 1001, "qr": "bar"}', '2013-06-01');