Trying Postgres partitioning for CLB

partition.sql

DROP SCHEMA PUBLIC CASCADE;
CREATE SCHEMA PUBLIC;

CREATE TABLE dataset (
  key serial PRIMARY KEY,
  title TEXT NOT NULL,
  created TIMESTAMP WITHOUT TIME ZONE DEFAULT NOW()
);

CREATE TABLE verbatim (
  key serial,
  dataset_key INTEGER NOT NULL,
  line INTEGER,
  file TEXT,
  terms jsonb
) PARTITION BY LIST (dataset_key);

CREATE TABLE name (
	key serial,
	id text,
  	dataset_key INTEGER NOT NULL,
  	verbatim_key INTEGER,
  	homotypic_key INTEGER DEFAULT currval('name_key_seq'::regclass) NOT NULL,
	sciname text NOT NULL
) PARTITION BY LIST (dataset_key);



-- DUMMY DATA
DO
$do$
BEGIN 
  	FOR i IN 1..12 LOOP
		-- create partitions
		EXECUTE 'CREATE TABLE verbatim_' || i || ' PARTITION OF verbatim FOR VALUES IN (' || i || ')';
		EXECUTE 'CREATE TABLE name_' || i || ' PARTITION OF name FOR VALUES IN (' || i || ')';
		-- insert data
		INSERT INTO dataset (key, title) SELECT i, 'dataset #' || i;
		INSERT INTO verbatim (key, dataset_key, line, file) SELECT x.key, i, x.key, 'name.txt' FROM generate_series(1, floor(exp(i))::int ) AS x(key);
		INSERT INTO name (key, id, verbatim_key, dataset_key, homotypic_key, sciname) SELECT x.key, 'id' || x.key, x.key, i, x.key, 'Abies alba Mill.' FROM generate_series(1, floor(exp(i))::int) AS x(key);
		-- create partition indices
		EXECUTE 'ALTER TABLE verbatim_' || i || ' ADD PRIMARY KEY (key)';
		EXECUTE 'ALTER TABLE name_' || i || ' ADD PRIMARY KEY (key)';
		EXECUTE 'ALTER TABLE name_' || i || ' ADD UNIQUE (id)';
		EXECUTE 'ALTER TABLE name_' || i || ' ADD FOREIGN KEY (homotypic_key) REFERENCES name_' || i;
	END LOOP;
END
$do$;