PQgetisnull : Not not return true even value is empty in DB

Question

Hi

I am working on bug in my Database library which use LibPQ internally.I see following behavior which looks strange to me :

For postgreSql Datatype DATE it returns true if its empty in DB For postgreSql Datatype TIME it return true if its empty in DB

But for VARCHAR,SMALLINT..it return false even they were empty in DB.

PS. I have not check for other data types yet

Answer 1

The empty string and zero (0) are NOT the same thing as NULL. Run a form of this query to see how many NULLs vs empty strings exist for your field:

SELECT COALESCE(<field_name>,'NULL'), COUNT(*)
FROM <table_name>
WHERE <field_name> = ''
   OR <field_name> IS NULL
GROUP BY 1

Replace the empty string ('') in this query with zero to check against numeric fields.