0

Doing by:

select * from ja_customers where id = 8154501

I receive:enter image description here

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?

0

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;
  • It returns me blank fields... Don't see anything - get_as_byte_array = null – user83914 Mar 20 '16 at 19:51
  • 1
    So, as a_horse_with_no_name says, you have fields with empty string values – Sahap Asci Mar 20 '16 at 20:21
  • Thanks for you help - This might be subject for another question but, what would be the cause of it? – user83914 Mar 20 '16 at 20:35
  • 1
    You should check your client application. For dba's point of view, just open logs for all queries for a while (log_min_duration_statement = 0 in postgresql.conf - dont do this in production db) and check which client sent those insert or update queries with empty string ('') value instead of null value. – Sahap Asci Mar 20 '16 at 20:40
  • Thanks! - If you have a better idea about how to find the statement - Because looking the logs for empty strings will not be easy. Anyway, have checked your answer as the correct one. – user83914 Mar 20 '16 at 20:57
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy