Blank (but not null) columns - PostgreSQL 9.2

Question

Doing by:

select * from ja_customers where id = 8154501

I receive:

You can see that all the required fields are "blank" with some invisible character, so they're not null, so they're valid (technically)

Question:

How can I see which characters are in there?

Answer 1

Create a function like the one below and check if a column has a value of empty string (this function return null for an empty string) or an invisible character.

    CREATE FUNCTION get_as_byte_array(anyelement) RETURNS INTEGER[] AS $$
      SELECT
        CASE 
          WHEN length($1::VARCHAR) = 0 THEN NULL
          ELSE
            (SELECT array_agg(get_byte($1::bytea, a)) FROM generate_series(0, length($1::bytea) - 1) a)
        END;
    $$ LANGUAGE SQL;

    SELECT get_as_byte_array(name_first), * FROM ja_customers WHERE id = 8154501;

Answer 2

You can use get_byte function to detect which character in specific offset:

SELECT *, get_byte(name_first,0) as firstChar,  get_byte(name_first,0) as secondChar FROM ja_customers WHERE id = 8154501