INSERT INTO pat_patient (id, dtype,
  first_name,
  last_name,
  alternate_first_name,
  alternate_last_name,
  gender_code,
  country_code,
  second_name,
  alternate_second_name,
  third_name,
  alternate_third_name,
  mother_maiden_name,
  alternate_mothers_maiden_name,
  character_set,
  creation_date,
  date_of_birth,
  race_code,
  religion_code,
  creator,
  contrast_allergies)
  SELECT
      id, 'om_patient',
     first_name,
     last_name,
     alternate_first_name,
     alternate_last_name,
     gender_code,
     country_code,
     second_name,
     alternate_second_name,
     third_name,
     alternate_third_name,
     mother_maiden_name,
     alternate_mothers_maiden_name,
     character_set,
     creation_date,
     date_of_birth,
     race_code,
     religion_code,
     creator,
    contrast_allergies
     FROM om_patient;

INSERT INTO pat_patient_address (id, address_type, is_main_address, country_code, street, street_number, zip_code, city, address_line, state, patient_id, dtype)
  SELECT
    nextval('pat_patient_address_id_seq'),
    6,
    true,
    country_code,
    street,
    NULL ,
    zip_code,
    city,
    street,
    state,
    id, 'patient_address'
  FROM om_patient;

SELECT pg_catalog.setval('pat_patient_id_seq', (select max(id) from pat_patient), true);


-- We need to remove the two constraints returned by the command below, constraint's name might differ from an installation to another, update the
-- "alter table" command accordingly.

SELECT
  tc.constraint_name, tc.table_name, kcu.column_name,
  ccu.table_name AS foreign_table_name,
  ccu.column_name AS foreign_column_name
FROM
  information_schema.table_constraints AS tc
  JOIN information_schema.key_column_usage AS kcu
    ON tc.constraint_name = kcu.constraint_name
  JOIN information_schema.constraint_column_usage AS ccu
    ON ccu.constraint_name = tc.constraint_name
WHERE constraint_type = 'FOREIGN KEY' AND ccu.table_name='om_patient';

-- constraint name might need to be changed, see output of above column
ALTER TABLE om_encounter DROP CONSTRAINT fkbd803412a1d1574d;
ALTER TABLE om_patient_identifier DROP CONSTRAINT fkff6f7224a1d1574d;

ALTER TABLE public.om_observation ALTER COLUMN value TYPE TEXT USING value::TEXT;

-- Execute the line below when everything is working properly
--DROP TABLE "om_patient";