Possible corrupt index?

Hi All

New on this list!

We have an existing postgres deployment which is showing some odd behaviour on Live. We use Navicat to manage it, and when we open a specific table via navicat on live, it says we don’t have a primary index – although the design view does show it. We have auto deployment scripts between environments, and none of the other environments show this. Doing a structure dump shows that there is indeed a primary index:

CREATE TABLE "public"."briefs_master" (

  "id" int4 NOT NULL DEFAULT nextval('briefs_master_id_seq'::regclass),

…..

"ext_system_ref" varchar(255) COLLATE "pg_catalog"."default"

…..

ALTER TABLE "public"."briefs_master" ADD CONSTRAINT "briefs_master_pkey" PRIMARY KEY ("id");

However, we are seeing very strange behaviour on live. We can search via the ID field just fine. Searching on ext_system_ref returns no records.

If I manually add data to the ext field, it then queries correctly. But as other records are added to the table, this field data disappears on the row I manually edited.

We have run a reindex on the specific index as well as table. We have run a vacuum on the table. Nothing seems to resolve and we are quite confused as to what the issue could be. Any help and guidance would be most appreciated

Regards

Zahir