NULL value causes blank row in SELECT results for text concatenation

Question

I have a query for producing some text for creating some VALUES text to put in a .sql file for inserting some rows. I get a blank line in the results:

postgres=# SELECT '  (' || obj_id || ', ''' || obj_type || '''),' FROM il2.objects WHERE obj_id < 11 ORDER BY obj_id;
        ?column?         
-------------------------
   (1, 'ScienceDomain'),

   (3, 'PIs'),
   (10, 'Instrument'),
(4 rows)

Doing a select *, it's pretty clear it's being caused by the obj_type being NULL for obj_id 2:

postgres=# SELECT * FROM il2.objects WHERE obj_id < 11;
 obj_id |   obj_type    
--------+---------------
     10 | Instrument
      1 | ScienceDomain
      2 | 
      3 | PIs
(4 rows)

(confirming it's NULL):

postgres=# SELECT * FROM il2.objects WHERE obj_type IS NULL;
 obj_id | obj_type 
--------+----------
      2 | 

Why is the result of the first SELECT giving me a blank row?
Even casting obj_type::text still gave me a blank row.

---

Additional Info: The schema, for what it's worth:

postgres=# \d il2.objects
                                  Table "il2.objects"
  Column  |       Type        | Collation | Nullable |             Default              
----------+-------------------+-----------+----------+----------------------------------
 obj_id   | integer           |           | not null | generated by default as identity
 obj_type | character varying |           |          | 
Indexes:
    "objects_pkey" PRIMARY KEY, btree (obj_id)

Answer 1

Why is the result of the first SELECT giving me a blank row?

Because concatenating NULL with any character type (or most other types, array types being a notable exception) results in NULL. Related:

• How can the concatenatation of a text and a NULL value return a non-null result?

The representation of NULL depends on your client. Some spell out NULL, some (incl. psql) put nothing instead. Often that's configurable.

Even casting obj_type::text still gave me a blank row.

Casting NULL to (almost) any type still returns NULL - of another data type.

I have a query for producing some text for creating some VALUES text to put in a .sql file for inserting some rows.

Have you considered COPY or the psql equivalent \copy?

Other solutions

If your example isn't simplified, you might just select whole ROW values:

SELECT o  -- whole row
FROM   il2.objects o
WHERE  obj_id < 11
ORDER  BY obj_id;

If you need that specific format, use format() to make it simple. Works with NULL values out of the box:

SELECT format('(%s, %L),', obj_id, obj_type)
FROM   objects;

You get NULL (unquoted) in place of NULL values (which is distinct from '' and may have to be distinguishable.)

db<>fiddle here (added to the existing fiddle of McNets, cudos)

Answer 2

Use COALESCE function to return the current value or an empty string.

SELECT '  (' || obj_id || ', ''' || coalesce(obj_type, '') || '''),' 
FROM   objects;

| ?column?        |
| :-------------- |
|   (1, 'val 1'), |
|   (2, ''),      |
|   (3, 'val 3'), |
|   (4, 'val 4'), |

db<>fiddle here