non-unique id numbers for some tables' primary keys ...

[tanksgt]

i want to load synthea sample data from all 50 states plus dc so i created a quick loop to generate 1000 patient records for each state and dc with each state's output placed in its own directory ... i wanted to confirm pre-load that the multi-state data is good to load noting that some tables have primary keys ... well i seem to have run into a problem with some tables not having truly unique ids ... there seem to be only 10 payer ids per state but the state files have different sizes so i know they are not all the same exact data except for the supposedly unique payer id ... organizations data also have non-unique ids as are patients data ssn ids ... does anybody have any tricks to fix these duplicated ids so i can load multi-state data into the database without any issues?

[ patients ssn is not a key so not required to be unique but still ... ]

postgres@oralinux940 | csv | 2025-05-07 10:54:02 EDT] $ for i in *_ids.list; do echo $i; echo "  normal count: $(wc -l < $i)"; echo "  uniq count:   $(sort -u $i | wc -l)"; done
careplans_ids.list
  normal count: 187966
  uniq count:   187966
claims_ids.list
  normal count: 6021419
  uniq count:   6021419
claims_transactions_ids.list
  normal count: 52039816
  uniq count:   52039816
encounters_ids.list
  normal count: 3303364
  uniq count:   3303364
organizations_ids.list
  normal count: 41336
  uniq count:   40973
patients_ids.list
  normal count: 57871
  uniq count:   57871
patients_ssn_ids.list
  normal count: 57871
  uniq count:   55911
payers_ids.list
  normal count: 510
  uniq count:   10
providers_ids.list
  normal count: 41336
  uniq count:   41336
postgres@oralinux940 | csv | 2025-05-07 11:16:48 EDT] $

[jawalonoski]

The payers should be duplicates. If you just load one of the files, the foreign keys will work for all states.

[tanksgt]

ok. i can do that. thank you.

how about the organizations ids? and the patients table ssn ids? i can disregard the patient ssns. and i can remove the primary key from the organizations id column but should i?

[jawalonoski]

The actual organizations are unique per state (they have physical geographic locations within state borders), even if somehow the IDs are not.

SSNs should be unique, so either we have some bug, or some of the randomization inputs should be changed between runs. In either case, disregard the duplicates if you can.

[tanksgt]

i searched the forums again but this time for 'unique IDs' then i redid the data generation with random seeds for population and clinicians for each state per #1189 ... aside from organizations.csv file and payers.csv file, all the other ids are showing uniqueness now ... i will just remove the primary key for the organization table for now ... thanks for the help ...

postgres@oralinux940 | csv | 2025-05-07 21:16:01 EDT] $ echo; for i in *_ids.list; do echo $i; echo "  normal count: $(wc -l < $i)"; echo "  uniq count:   $(sort -u $i | wc -l)"; done; echo

careplans_ids.list
  normal count: 190058
  uniq count:   190058
claims_ids.list
  normal count: 6077721
  uniq count:   6077721
claims_transactions_ids.list
  normal count: 52440293
  uniq count:   52440293
encounters_ids.list
  normal count: 3318399
  uniq count:   3318399
organizations_ids.list
  normal count: 41316
  uniq count:   40947
patients_ids.list
  normal count: 58082
  uniq count:   58082
patients_ssn_ids.list
  normal count: 58082
  uniq count:   58082
payers_ids.list
  normal count: 510
  uniq count:   10
providers_ids.list
  normal count: 41316
  uniq count:   41316

postgres@oralinux940 | csv | 2025-05-07 21:23:17 EDT] $