postgresql - Postgres value has character which i am not able to identify

Question

Currently i have a table which i am migrating to a higher version of postgres DB. And during migration we got constraint violation. We migrated without constraint in the target db it worked. My query the below id caused the error pulled it from the logs.

select * from test where id like 'test1%';

The above query returns two records and

select char_length(id), * from test where id like 'test1%';

The above query returns two records with same char_length

select * from test where id = 'test1 ';

The above query returns zero records

So what is the character at the end which is present. Please advice

question from:https://stackoverflow.com/questions/65935543/postgres-value-has-character-which-i-am-not-able-to-identify

Answer 1

I assume that the length of the string is 6. You can get the sixth character and its hexadecimal encoded value with

SELECT substr(id, 6, 1),
       CAST(substr(id, 6, 1) AS bytea)
FROM test
WHERE id LIKE 'test1%';