# Identifying the Correct Unique Patient Identifier After Exporting Data from PostgreSQL

**URL:** <https://forum.communityhealthtoolkit.org/t/identifying-the-correct-unique-patient-identifier-after-exporting-data-from-postgresql/5679>\
**Category:** Technical Support\
**Tags:** forms, database\
**Created:** [July 9, 2026, 9:54am UTC](https://forum.communityhealthtoolkit.org/t/identifying-the-correct-unique-patient-identifier-after-exporting-data-from-postgresql/5679 "2026-07-09T09:54:45Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![Sanjit7](https://communityhealthtoolkit.b-cdn.net/user_avatar/forum.communityhealthtoolkit.org/sanjit7/32/2003_2.png) [@Sanjit7](https://forum.communityhealthtoolkit.org/u/Sanjit7)\
**Post date:** [July 9, 2026, 9:54am UTC](https://forum.communityhealthtoolkit.org/t/identifying-the-correct-unique-patient-identifier-after-exporting-data-from-postgresql/5679/1 "2026-07-09T09:54:45Z")

</div>

### Identifying the Correct Unique Patient Identifier After Exporting Data from PostgreSQL

I exported patient-related data from a PostgreSQL database and am trying to determine the correct unique identifier for each patient.

The issue is that many records have blank or missing IDs. In addition, I am seeing cases where the same patient appears multiple times with:

- The same name

- The same mobile number

- Different `patient_id` values

This makes it difficult to determine which field should be treated as the true unique identifier.

For example:

| Name | Mobile Number | patient\_id |
| --- | --- | --- |
| John Doe | 9800000000 | P001 |
| John Doe | 9800000000 | P002 |

I also have separate patient information and form/submission data tables, but many of the ID fields are blank in the exported data.

My questions are:

1. Which field is typically considered the actual unique identifier in this type of system?

2. If `patient_id` values can differ for the same person, what is the best way to identify duplicate patient records?

3. Are there database-level IDs (e.g., `_id`, UUID, primary key) that I should be looking for instead of `patient_id`?

4. What queries or checks would you recommend to determine which identifier is truly unique and reliable?

Any guidance on how to verify the correct patient identifier and safely merge patient records would be greatly appreciated.

---

<div class="post-metadata">

**Author:** ![kalama\_kitsao](https://communityhealthtoolkit.b-cdn.net/letter_avatar_proxy/v4/letter/k/cdc98d/32.png) [@kalama\_kitsao](https://forum.communityhealthtoolkit.org/u/kalama_kitsao)\
**Post date:** [July 10, 2026, 3:26am UTC](https://forum.communityhealthtoolkit.org/t/identifying-the-correct-unique-patient-identifier-after-exporting-data-from-postgresql/5679/2 "2026-07-10T03:26:18Z")

</div>

Hello @Sanjit7

The answer to this question starts before PostgreSQL.

#### The Key Point

In a CHT implementation, the “correct patient identifier” is usually defined by the application configuration, forms, and workflows — **not by PostgreSQL itself**.

PostgreSQL (via CHT Sync / couch2pg) simply contains what was captured and synchronized from CHT.

Before asking:

> Which field in PostgreSQL is the true patient identifier?

it is important to first understand:

> How was patient identity designed and configured in the source CHT application?

* * *

#### 1. The Identifier Is Defined Upstream

Different CHT implementations use different identifiers, for example:

- `patient_id`
- `national_id`
- `medical_record_number`
- Generated codes
- Phone numbers
- Contact UUIDs

The CHT platform does not enforce a universal patient identifier.

Whether any of these fields are unique depends entirely on:

- Contact configuration
- Registration forms
- Workflow design
- Validation rules

Relevant documentation:

- Contact Management: [Contact and User Management – Community Health Toolkit](https://docs.communityhealthtoolkit.org/building/contact-management/)
- Forms: [app – Community Health Toolkit](https://docs.communityhealthtoolkit.org/building/forms/app/)

* * *

#### 2. Different Patient IDs Do Not Necessarily Mean the Identifier Is Wrong

You mentioned seeing:

- Same name
- Same phone number
- Different `patient_id` values

If `patient_id` was intended to uniquely identify patients, then both IDs may still be valid.

The more important question becomes:

> How were two patient records created for what appears to be the same individual?

This is usually a workflow or data quality question rather than a PostgreSQL question.

For example, two users may independently register the same person, resulting in two separate patient records with different identifiers.

* * *

#### 3. Understanding `_id`

Every CouchDB document contains an `_id` field.

```json
{
  "_id": "..."
}

```

This value is always unique.

However, `_id` identifies a **document** , not necessarily a patient.

For example:

- A patient contact has its own `_id`
- A report submission has its own `_id`
- A task has its own `_id`

Therefore:

> `_id` is a reliable document identifier, but it should not automatically be treated as the patient identifier.

* * *

#### 4. Blank Identifier Fields May Be Expected

You mentioned that some patient or submission tables contain blank ID fields.

In CHT, patient identifiers are not automatically copied into report submissions.

If a project wants fields such as:

- `patient_id`
- `patient_uuid`
- `contact_uuid`

to appear in reports, those values must be explicitly included in the form configuration.

Relevant documentation:

- Forms: [app – Community Health Toolkit](https://docs.communityhealthtoolkit.org/building/forms/app/)
- Form Context: [https://docs.communityhealthtoolkit.org/building/forms/form-context/](https://docs.communityhealthtoolkit.org/building/forms/form-context/)

As a result:

> Blank values in PostgreSQL often mean the identifier was never captured in the source form.

* * *

#### 5. PostgreSQL Only Contains What Was Submitted

CHT Sync replicates data from CouchDB into PostgreSQL.

It does not generate missing identifiers or reconstruct relationships that were not captured.

If an identifier is missing in PostgreSQL, the first thing to check is whether it exists in the original document.

Relevant documentation:

- Analytics Overview: [PostgreSQL Analytics – Community Health Toolkit](https://docs.communityhealthtoolkit.org/technical-overview/data/analytics/)

* * *

#### 6. There Is No Universal Answer to “Which Field Is Truly Unique?”

The answer depends on how the implementation was designed.

In one project, `patient_id` may be the authoritative identifier.

In another, it may be a contact UUID, national ID, or another field entirely.

Without reviewing the application’s configuration and forms, it is difficult to determine which field was intended to uniquely identify patients.

* * *

#### 7. What Should Be Investigated First?

Before attempting deduplication or record merging, I would recommend understanding:

- Which form creates patient records?
- How is `patient_id` generated?
- Is uniqueness enforced during registration?
- Are duplicate checks implemented?
- Which field was intended to uniquely identify a patient?

These answers are usually found in the CHT configuration rather than the PostgreSQL database.

* * *

#### The practical question is often:

> Can I determine the correct patient identifier purely from PostgreSQL exports?

In most CHT implementations, the answer is:

**Not reliably.**

PostgreSQL can help identify potential duplicates and assess data quality, but determining the intended patient identifier usually requires reviewing:

- Contact configuration
- Registration workflows
- Form design
- Identifier generation logic

within the CHT application itself.

Only then can you confidently determine whether `patient_id`, a contact UUID, a national identifier, or another field was intended to be the authoritative patient identifier.