NULL value causes blank row in SELECT results for text concatenation
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
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)