I have this table:
dbchatbot=# d messages
Table "public.messages"
Column | Type | Collation | Nullable | Default
-----------------+-----------------------------+-----------+----------+---------
uuid | character varying(36) | | not null |
content | text | | |
Indexes:
"messages_pkey" PRIMARY KEY, btree (uuid)
"messages_uuid_idx" UNIQUE, btree (uuid)
*(I know I should be using a uuid
type field, but I am inheriting this table and I can not change this.)
Some times when I am querying by uuid
the match fails:
select uuid from messages where uuid = '3UUkCMIw3IzrYuwLnezDvL';
uuid
------
(0 rows)
It works if I use ilike
:
select uuid from messages where uuid ilike '3UUkCMIw3IzrYuwLnezDvL';
uuid
------------------------
3UUkCMIw3IzrYuwLnezDvL
(1 row)
Or if I trim the field value:
select uuid from messages where trim(uuid) = '3UUkCMIw3IzrYuwLnezDvL';
uuid
------------------------
3UUkCMIw3IzrYuwLnezDvL
(1 row)
It doesn’t fail in all records of the table:
select uuid from messages where uuid = '3wvMFFjZLD4aIsj64yLScA';
uuid
------------------------
3wvMFFjZLD4aIsj64yLScA
(1 row)
I can not use ilike
or trim
because I am using a ORM and I don’t want to mess up with the primary keys default search.
How can I fix the issue?
Update
Another example to demonstrate that there is not leading or trailing spaces:
select "uuid", length("uuid"), '>' || "uuid" || '<' AS debug_uuid from messages where "uuid" ilike '3UUkCMIw3IzrYuwLnezDvL';
uuid | length | debug_uuid
------------------------+--------+--------------------------
3UUkCMIw3IzrYuwLnezDvL | 22 | >3UUkCMIw3IzrYuwLnezDvL<
(1 row)
More weird the db doesn’t return any record by this filter:
select uuid from messages where uuid <> trim(uuid);
uuid
------
(0 rows)
Update 2
More tests:
select uuid, length(uuid), length(trim(uuid)) from messages where length(uuid) <> length(trim(uuid));
uuid | length | length
------+--------+--------
(0 rows)
select uuid, encode(uuid::bytea, 'hex') as uuid_hex, encode('3UUkCMIw3IzrYuwLnezDvL', 'hex') as uuid_hex from messages where trim(uuid) = '3UUkCMIw3IzrYuwLnezDvL';
uuid | uuid_hex | uuid_hex
------------------------+----------------------------------------------+----------------------------------------------
3UUkCMIw3IzrYuwLnezDvL | 3355556b434d497733497a725975774c6e657a44764c | 3355556b434d497733497a725975774c6e657a44764c
(1 row)
Update3
It is not the field. It is the index that has an issue. If I deactivate the indexes the query returns the record:
BEGIN;
SET LOCAL enable_indexscan = OFF;
SET LOCAL enable_bitmapscan = OFF;
select uuid from messages where uuid = '3UUkCMIw3IzrYuwLnezDvL';
uuid
------------------------
3UUkCMIw3IzrYuwLnezDvL
(1 row)
COMMIT;
2
Answers
The index was corrupted. Reindexing the table fixed the issue:
It seems that some UUIDs have useless leading or trailing spaces. Just fix them: