Xjyuk

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)

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)

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