-1

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

Learner
  • 237
  • 4
  • 15
  • Could be a collation/client-encoding thing. Easiest way to analyse it: `copy (select ... WHERE id LIKE ... ) TO 'test.file' ...;` And then hexdump the file – wildplasser Jan 28 '21 at 11:47

1 Answers1

0

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%';
Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • Yes the length of string is 5 and storing in a varchar field. The above query returned same result. Not able to make out the charaacter. I am doing it right? – Learner Jan 28 '21 at 11:22
  • I see. There is a lot of relevant information missing. Edit your question and add it: 1) what is the definition of the constraint? 2) if the length of the strings is 5, why are you looking for extra characters after `test1`? 3) what is the result you get for my query? – Laurenz Albe Jan 28 '21 at 11:43