PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How can I specify a non-null default value for a Scalar Subquery?

I have a query of the form:

SELECT 
col1,
col2,
(
SELECT my_value FROM my_second_table WHERE my_table.value = ...
) AS potentially_null_column

FROM my_first_table

When SELECT my_value FROM my_second_table WHERE my_table.value = ... returns no rows, then potentially_null_column is set to null.

This jibes with the Postgres docs on Scalar Subqueries: https://www.postgresql.org/docs/8.3/sql-expressions.html#SQL-SYNTAX-SCALAR-SUBQUERIES:

But if, during a particular execution, the subquery returns no rows, there is no error; the scalar result is taken to be null.)

The problem is, I want potentially_null_column to be a string 'UNSET' instead of null if there are no rows returned. Unfortunately, coalesce won’t work here, since there are no rows returned to coalesce. How can I specify a non-null default value for a Scalar Subquery?

Tags:

2

Answers


  1. 0

    As said in comments, the COALESCE function is what you are looking for:

    SELECT *,
       COALESCE((
          SELECT potentially_null_column
          FROM my_second_table
          WHERE [...]
       ), 'UNSET')
FROM my_first_table

    SQL does not allow for mixed types to be returned in a single column, so if your data is not VARCHAR/TEXT and similar, you are not allows to return a constant string instead of NULL.
    For instance, if your column is INTEGER/NUMERIC, you can replace NULL by 0, not by 'UNSET':

    SELECT *,
       COALESCE((
          SELECT potentially_null_column
          FROM my_second_table
          WHERE [...]
       ), 0)
FROM my_first_table
    Login or Signup to reply.
  2. 0

    Combine that with a union: demo at db<>fiddle

    SELECT  col1,
        col2,
        (SELECT _val FROM
         (SELECT 0 AS _order,coalesce(my_value,'FOUND UNSET') _val
          FROM my_second_table 
          WHERE my_table.value = v2
          UNION ALL SELECT 1 AS _order,'NOT FOUND' as _val) 
         ORDER BY _order LIMIT 1
         ) AS potentially_null_column
FROM my_first_table as my_table
    col1 col2 potentially_null_column
    10 1 my_value_1
    20 2 NOT FOUND
    40 4 FOUND UNSET

    If you just move coalesce() to wrap the scalar subquery from the outside, it’ll work but it won’t care whether it doesn’t find a matching row at all, or if it does but with my_value set to null in it – in both cases it’ll report 'UNSET' just the same, obscuring potentially important information:

    SELECT  col1,
        col2,
        coalesce( (SELECT my_value FROM my_second_table WHERE my_table.value = v2)::text
                 ,'UNSET') AS potentially_null_column
FROM my_first_table as my_table
    col1 col2 potentially_null_column
    10 1 my_value_1
    20 2 UNSET
    40 4 UNSET

    UNION can let you differentiate between these cases, so you can use coalesce() inside the scalar subquery if it’s FOUND UNSET, while failover value in UNION can give you NOT FOUND. If it doesn’t matter or you intentionally wanted to merge the two cases, moving coalesce() up/out is enough.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search