A table in my database (PostgreSQL 9.6) has a mixture of NULL and not null values, which I need to COALESCE() as a part of the creation of another attribute during insert into a resulting dimension table. However, Postgres seems unable to recognize the NULL values as NULL.
SELECT DISTINCT name, description
FROM my_table
WHERE name IN('STUDIO', 'ONE BEDROOM')
AND description IS NOT NULL;
returns
| name | description |
|---|---|
| STUDIO | NULL |
| ONE BEDROOM | NULL |
Whereas
SELECT DISTINCT name, description
FROM my_table
WHERE name IN('STUDIO', 'ONE BEDROOM')
AND description IS NULL;
returns
| name | description |
|---|
as such, something like
SELECT DISTINCT name, COALESCE(description, 'N/A')
FROM my_table
WHERE name IN('STUDIO', 'ONE BEDROOM');
will return
| name | coalesce |
|---|---|
| STUDIO | NULL |
| ONE BEDROOM | NULL |
instead of the expected
| name | coalesce |
|---|---|
| STUDIO | N/A |
| ONE BEDROOM | N/A |
The DDL for these attributes is fairly straightforward:
...
name text COLLATE pg_catalog."default",
description text COLLATE pg_catalog."default",
...
I've already checked whether the attribute was filled with 'NULL' rather than being an actual NULL value, and that's not the case. I've also tried quoting the attribute in question as "description" and that hasn't made a difference. Casting to VARCHAR hasn't helped (I thought it might be the fact that it's a TEXT attribute). If I nullify some values in the other text column (name) I'm able to coalesce with a test value, so that one is seemingly behaving as expected leading me to think it's not a data type issue. This table exists in multiple databases on multiple servers and exhibits the same behavior in all of them.
I've tried inserting into a new table that has different attribute definitions:
...
floorplan_name "character varying(128)" COLLATE pg_catalog."default" NOT NULL DEFAULT 'Unknown'::character varying,
floorplan_desc "character varying(256)" COLLATE pg_catalog."default" NOT NULL DEFAULT 'Not Provided'::character varying,
...
resulting in
| name | coalesce |
|---|---|
| STUDIO | NULL |
| ONE BEDROOM | NULL |
so, not only is the default value unable to populate, leaving the values NULL in an attribute that is defined as NOT NULL, but the example SELECT statements above all behave in exactly the same way when run against the new table.
Does anyone have any idea what might be causing this?
pg_dumpof the table produce?select length(description);? If you get0then you have empty strings. If you getNULLthen you haveNULLvalues. I'm betting on empty strings. InSTUDIO NULLare you just saying 'NULL' because you don't see a value?psqlto confirm query behavior.psqlis written by the same folks that write the server code, so it is the best way to test whether something is working or not.