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?
1
It seems that some UUIDs have useless leading or trailing spaces. Just fix them:
update messages set
uuid = trim(uuid)
where uuid <> trim(uuid);
3