Postgres 11 query shows blank rows due to NULL values

Question

Issue: Executing a query that generates INSERT statements to migrate data from Postgres to Oracle. Results show the expected number of rows, except more and more rows are blank. NULL values in the data appear to be the cause of the blank rows. Sample (contrived) data (3 rows):

Id   FirstName  LastName   PhoneNumber   Gender  City
1    Fred       Flinstone  1111111111    M       Bedrock
2    Barney     Rubble     <no Ph#>      M       Bedrock
3    Wilma      Flinstone  2222222222    F       <No City>       

Execute this type of query from the Postgres psql prompt:

postgresdb=# Select 'INSERT into TABLE1.POSTGRESXFER VALUES ('||"Id"||', '''||"FirstName"||', '''||"PhoneNumber"||', '''||"Gender"||', '''||"City||''');' from "PostgresSourceDB"."SourceTable";

This should create an Oracle compliant INSERT statement for each row. It does not. This is what I will get:

Insert into TABLE1.POSTGRESXFER VALUES (1, 'Fred', 'Flinstone', '1111111111', 'M', 'Bedrock');
<blank row with empty strings for all values, caused by blank PhoneNumber based on testing>
<blank row with empty strings for all values, caused by blank City based on testing>

I have never seen concatenations cause problems with a query. What makes this more confusing is if I run either one of the two queries below, I see all of the data, to include blank (null values) without issue:

Select * from "PostgresSourceDB"."SourceTable";
Select "Id", "FirstName", "LastName", "PhoneNumber", "Gender", "City";

Anyone have an idea why the concatenations I'm using are causing Postgres to show blank rows?

Answer 1

Any expression involving NULL yields NULL e.g. 'foo'||null||'bar' returns null, just like 4 + null + 2

Oracle doesn't comply with the SQL standard with regards to NULL values and varchar values, so concatenating a varchar with NULL in Oracle, does not yield NULL. Instead NULL is treated like an empty string in that case.

So you need to use coalesce() to turn NULL values in to the NULL keyword, e.g.Alternatively you can use coalesce() in your original query, e.g. coalesce("PhoneNumber", 'NULL').

But you should use quote_literal() to properly deal with embedded single quotes in the column values

Something like:

Select concat('INSERT INTO into TABLE1.POSTGRESXFER values (', 
              concat_ws(',', "Id", 
                        quote_literal("FirstName"), 
                        quote_literal("LastName"), 
                        coalesce(quote_literal("PhoneNumber"), 'NULL'), 
                        coalesce(quote_literal("Gender"), 'NULL'), 
                        coalesce(quote_literal("City"), 'NULL')),
               ');') 
from "SourceTable";

Another option is to use the format() function which makes writing statements like that much easier:

select format('insert into TABLE1.POSTGRESXFER values (%L, %L, %L, %L, %L, %L);', 
              "Id", "FirstName", "LastName", "PhoneNumber", "Gender", "City") 
from "SourceTable"

Returns:

format                                                                                          
------------------------------------------------------------------------------------------------
insert into TABLE1.POSTGRESXFER values ('1', 'Fred', 'Flinstone', '1111111111', 'M', 'Bedrock');
insert into TABLE1.POSTGRESXFER values ('2', 'Barney', 'Rubble', NULL, 'M', 'Bedrock');         
insert into TABLE1.POSTGRESXFER values ('3', 'Wilma', 'Flinstone', '2222222222', 'F', NULL);    

format() will also correctly deal with single quotes inside your values.