6

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)
8

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:

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)

  • 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 Aug 1 at 18:04
11

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.