0

We are migrating DB2 data to PostgreSQL 11.x using AWS DMS, we have varchar fields in db2 with trailing spaces and without any TRIM these fields working fine when we are using these fields in a WHERE clause. I think DB2 internally trimming them as these are varchar fields. But after moving to PostgreSQL these fields are not working without TRIM and also some times these giving unexpected results even if you use TRIM. below is the detailed problem.

Source: DB2 - RECIP_NUM -- VARCHAR(10) -- 'ST001 '

select RECIP_NUMBER, SERV_TYPE, LENGTH(SERV_TYPE) AS before_trim_COL_LENGTH, LENGTH(trim(SERV_TYPE)) AS after_trim_COL_LENGTH
from serv_type rst
WHERE SERV_TYPE = 'ST001' -- THIS WORKS FINE WITHOUT TRIM

Output:Output of DB2

Target: PGSQL -- RECIP_NUM -- VARCHAR(10) -- 'ST001 '

select RECIP_NUMBER, SERV_TYPE, LENGTH(SERV_TYPE) AS COL_LENGTH 
from serv_type rst
WHERE trim(SERV_TYPE) = 'ST001' -- THIS IS NOT GIVING ANY OUTPUT WITHOUT TRIM

Output: Output of PostgreSQL

Is there any way we can tell PostgreSQL to ignore the trailing spaces of a VARCHAR Column?

mustaccio
  • 18,234
  • 16
  • 48
  • 57
vijay
  • 1

1 Answers1

1

Postgres doesn't follow the SQL standard, which requires the shorter string be padded, when comparing VARCHAR or TEXT strings; it only pads the CHAR strings. Therefore, you can use ...WHERE SERV_TYPE::char = 'ST001'::char to simulate the Db2 behaviour. Note though that this will preclude the use of index on SERV_TYPE, same as when using trim(SERV_TYPE).

mustaccio
  • 18,234
  • 16
  • 48
  • 57
  • Strictly speaking, I don't think that comment about the SQL standard is true (anymore?). The 2011 standard says "The comparison of two character string expressions depends on the collation used for the comparison". Which means the behaviour changes depending on if your collations is the using PAD or NO PAD characteristic. I.e. for NO PAD, "[if] the shorter value is equal to some prefix of the longer value, then the shorter value is considered less than the longer value". For PAD, "the shorter value is effectively extended to the length of the longer by concatenation of spaces on the right." – Paul Vernon Jan 04 '21 at 17:24