HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlModerate

NULL value causes blank row in SELECT results for text concatenation

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
nulltextblankvalueforresultsselectrowconcatenationcauses

Problem

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)

Solution

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

Code Snippets

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

Context

StackExchange Database Administrators Q#244345, answer score: 11

Revisions (0)

No revisions yet.