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)

Erwin Brandstetter · Accepted Answer · 2022-08-15 23:41:48Z

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, kudos)

I didn't know about format() - nice. I hadn't considered COPY; in a previous job, the convention was always to do inserts with the VALUES convention, but that's an excellent alternative, too - thanks. — Randall
– Randall, Commented Aug 1, 2019 at 18:04

McNets · Accepted Answer · 2019-08-01 15:58:53Z

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

Stack Exchange Network

NULL value causes blank row in SELECT results for text concatenation

2 Answers 2

Other solutions

Linked

Hot Network Questions