-- секционированная таблица
CREATE TABLE ru.propose_sod (
  id BIGSERIAL,
  receive_id INTEGER DEFAULT 0 NOT NULL,
  date_begin DATE NOT NULL,
  balance_begin REAL DEFAULT 0 NOT NULL,
  quantity_credit REAL DEFAULT 0 NOT NULL,
  price_credit REAL DEFAULT 0 NOT NULL,
  quantity_debit REAL DEFAULT 0 NOT NULL,
  price_debit REAL DEFAULT 0 NOT NULL,
  drugs_sha1 VARCHAR(40),
  flag_set SMALLINT DEFAULT 0 NOT NULL
) 
PARTITION BY RANGE (date_begin)
WITH (oids = false);
-- секция
CREATE TABLE private.propose_sod_ru_2019 PARTITION OF ru.propose_sod(
  CONSTRAINT pk_propose_sod_ru_2019 PRIMARY KEY(id),
  CONSTRAINT fk_propose_sod_ru_2019_r FOREIGN KEY (receive_id)
    REFERENCES ru.receive_day(id)
    ON DELETE CASCADE
    ON UPDATE NO ACTION
    NOT DEFERRABLE
) 
FOR VALUES FROM ('2019-01-01') TO ('2020-01-01')
WITH (oids = false)
TABLESPACE arch_global;

CREATE INDEX propose_sod_ru_2019_date_begin_idx ON private.propose_sod_ru_2019
  USING btree (date_begin)
  TABLESPACE arch_global;

CREATE INDEX propose_sod_ru_2019_drugs_sha1 ON private.propose_sod_ru_2019
  USING btree (drugs_sha1 COLLATE pg_catalog."default")
  TABLESPACE arch_global;

CREATE INDEX propose_sod_ru_2019_rcv_id ON private.propose_sod_ru_2019
  USING btree (receive_id)
  TABLESPACE arch_global;
-- мастер
CREATE TABLE ru.receive_day (
  id SERIAL,
  organization_id INTEGER DEFAULT 0 NOT NULL,
  date_receive TIMESTAMP WITHOUT TIME ZONE DEFAULT now(),
  date_begin DATE,
  date_end DATE,
  price_sum DOUBLE PRECISION DEFAULT 0 NOT NULL,
  total_positions INTEGER DEFAULT 0 NOT NULL,
  unknown_positions INTEGER DEFAULT 0 NOT NULL,
  hash_data VARCHAR(40) DEFAULT ''::character varying NOT NULL,
  flag_set INTEGER DEFAULT 0 NOT NULL,
  meta JSONB,
  CONSTRAINT pk_ps_receive_so_ru PRIMARY KEY(id)
) 
WITH (oids = false);
CREATE INDEX ps_receive_so_ru_idx_db ON ru.receive_day
  USING btree (date_begin);

CREATE INDEX ps_receive_so_ru_idx_o ON ru.receive_day
  USING btree (organization_id);

alter table private.propose_sod_ru_2019 add constraint tmp_attach check (date_begin >= '2019-01-01' and date_begin < '2019-11-01') not valid;
alter table private.propose_sod_ru_2019 validate constraint tmp_attach;
alter table ru.propose_sod DETACH PARTITION  private.propose_sod_ru_2019;
alter table ru.propose_sod ATTACH PARTITION private.propose_sod_ru_2019 FOR VALUES FROM ('2019-01-01') TO ('2019-11-01');
alter table private.propose_sod_ru_2019 drop constraint tmp_attach;