This warning is added because bpchar type (“blank-padded char”) may have some functional equivalence difference compared to the varchar data type in Snowflake. However, both data types can store the values up to the “n” length of characters and consume storage for only the amount of actual data stored. The main difference occurs when there are blanks at the end of the data, where bpchar does not store them but snowflake does.
For this reason, we can use the RTRIM function so that these blanks are not stored. But there may be cases where the functionality is not completely equivalent.
SELECT 'Y'::BPCHAR;
SELECT 'Y '::BPCHAR(20);
SELECT COL1::BPCHAR(20) FROM tbl;
Output Code:
OUT -> PostgreSQL_01.sql
CREATE TABLE table1 (
col1 VARCHAR /*** SSC-FDM-PG0002 - BPCHAR CONVERTED TO VARCHAR. THESE TYPES MAY HAVE SOME FUNCTIONAL DIFFERENCES. ***/,
col2 VARCHAR(20) /*** SSC-FDM-PG0002 - BPCHAR CONVERTED TO VARCHAR. THESE TYPES MAY HAVE SOME FUNCTIONAL DIFFERENCES. ***/
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": { "major": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "postgresql", "convertedOn": "09/17/2024" }}';
OUT -> PostgreSQL_02.sql
SELECT 'Y':: VARCHAR /*** SSC-FDM-PG0002 - BPCHAR CONVERTED TO VARCHAR. THESE TYPES MAY HAVE SOME FUNCTIONAL DIFFERENCES. ***/;
SELECT
RTRIM( 'Y ') :: VARCHAR(20) /*** SSC-FDM-PG0002 - BPCHAR CONVERTED TO VARCHAR. THESE TYPES MAY HAVE SOME FUNCTIONAL DIFFERENCES. ***/;
SELECT
RTRIM( COL1) :: VARCHAR(20) /*** SSC-FDM-PG0002 - BPCHAR CONVERTED TO VARCHAR. THESE TYPES MAY HAVE SOME FUNCTIONAL DIFFERENCES. ***/
FROM
tbl;
The rtrimfunction can resolve storage differences in case you want those blanks not to be stored. This case is handled in the , however, there may be other scenarios where it has to be handled manually. For more information refer to the Snowflake documentation about .