OMOP CDM PostgreSQL database with example data

omop-cdm-postgres.sh

#!/bin/sh
# First run postgres, e.g.
# nerdctl run -it --rm -p5432:5432 -e POSTGRES_PASSWORD=password docker.io/library/postgres:17

set -eu

# Postgres connection details
PG_HOST=127.0.0.1
PG_USER=postgres
PG_SCHEMA=public
export PGPASSWORD=password

if [ ! -f OMOPCDM_v5.4.zip ]; then
  wget https://github.com/OHDSI/CommonDataModel/releases/download/v5.4.2/OMOPCDM_v5.4.zip
fi
unzip -d omopcdm OMOPCDM_v5.4.zip

if [ ! -f Synthea27Nj_5.4.zip ]; then
  wget https://github.com/OHDSI/EunomiaDatasets/raw/refs/heads/main/datasets/Synthea27Nj/Synthea27Nj_5.4.zip
fi
unzip -d synthea27nj Synthea27Nj_5.4.zip


# Setup tables and primary keys
sed s/@cdmDatabaseSchema/$PG_SCHEMA/g omopcdm/5.4/postgresql/OMOPCDM_postgresql_5.4_ddl.sql | psql -h"$PG_HOST" -U"$PG_USER" -f -
sed s/@cdmDatabaseSchema/$PG_SCHEMA/g omopcdm/5.4/postgresql/OMOPCDM_postgresql_5.4_primary_keys.sql | psql -h"$PG_HOST" -U"$PG_USER" -f -

# Import data table by table
for f in synthea27nj/*csv; do
  TABLE=$(echo "${f#*/}" | cut -d. -f1 | tr A-Z a-z)
  echo "Loading $f to table $TABLE"
  psql -h"$PG_HOST" -U"$PG_USER" -v ON_ERROR_STOP=1 -c "\\copy $TABLE FROM '$f' WITH CSV HEADER;" || break
done

# Setup constraints and indices
sed s/@cdmDatabaseSchema/$PG_SCHEMA/g omopcdm/5.4/postgresql/OMOPCDM_postgresql_5.4_constraints.sql | psql -h"$PG_HOST" -U"$PG_USER" -f -
sed s/@cdmDatabaseSchema/$PG_SCHEMA/g omopcdm/5.4/postgresql/OMOPCDM_postgresql_5.4_indices.sql | psql -h"$PG_HOST" -U"$PG_USER" -f -