CREATE TABLE public.device (
  device_uuid UUID NOT NULL,
  device_name VARCHAR NOT NULL,
  device_type VARCHAR NOT NULL,
  CONSTRAINT device_pkey PRIMARY KEY (device_uuid ASC)
);

CREATE TABLE public.device_configrule (
  configrule_uuid UUID NOT NULL,
  device_uuid UUID NOT NULL,
  "position" INT8 NOT NULL,
  data VARCHAR NOT NULL,
  CONSTRAINT device_configrule_pkey PRIMARY KEY (configrule_uuid ASC),
  CONSTRAINT device_configrule_device_uuid_fkey FOREIGN KEY (device_uuid) REFERENCES public.device(device_uuid) ON DELETE CASCADE,
  INDEX device_configrule_device_uuid_rec_idx (device_uuid ASC) STORING ("position", data),
  CONSTRAINT check_position_value CHECK ("position" >= 0:::INT8)
);

INSERT INTO device (device_uuid, device_name, device_type) VALUES
('a3645f8a-5f1f-4d91-8b11-af4104e57f52'::UUID, 'R1', 'router'),
('7c1e923c-145c-48c5-8016-0d1f596cb4c1'::UUID, 'R2', 'router')
ON CONFLICT (device_uuid) DO UPDATE SET device_name=excluded.device_name, device_type=excluded.device_type;

EXPLAIN INSERT INTO device_configrule (configrule_uuid, device_uuid, "position", data) VALUES
('bcce5f0e-5f72-4e1f-8132-42b25e515c77'::UUID, 'a3645f8a-5f1f-4d91-8b11-af4104e57f52'::UUID, 1, 'this is rule 1'),
('e881d6cc-f732-4dcd-87db-d2ba43e47c5c'::UUID, 'a3645f8a-5f1f-4d91-8b11-af4104e57f52'::UUID, 2, 'this is rule 2'),
('571f6477-fee5-43c8-91fd-b2b290bdb54f'::UUID, 'a3645f8a-5f1f-4d91-8b11-af4104e57f52'::UUID, 3, 'this is rule 3')
ON CONFLICT (configrule_uuid) DO UPDATE SET "position"=excluded."position", data=excluded.data
RETURNING device_configrule.data;

EXPLAIN INSERT INTO device_configrule (configrule_uuid, device_uuid, "position", data) VALUES
('36b48fca-c232-478f-a66c-8967df2e559d'::UUID, '7c1e923c-145c-48c5-8016-0d1f596cb4c1'::UUID, 1, 'this is rule 1'),
('b1278eae-a5c6-4a0a-a6f3-2508c9fa39c7'::UUID, '7c1e923c-145c-48c5-8016-0d1f596cb4c1'::UUID, 2, 'this is rule 2'),
('d953d1b6-7c1d-4a80-bcfe-3d920874bbd8'::UUID, '7c1e923c-145c-48c5-8016-0d1f596cb4c1'::UUID, 3, 'this is rule 3')
ON CONFLICT (configrule_uuid) DO UPDATE SET data = excluded.data RETURNING device_configrule.data;