PSQL: How can I return a value from a select query, but if the resultset is null return a default vaule?

  DECLARE _retention bigint := (SELECT retention_days
          FROM   retention_period
          WHERE  fulfilment_type = 'daily');

If the SELECT query returns null because "daily" doesn’t exist in the retention_period table, how can I return a default value please?

>Solution :

You can use Postgres coalesce to set a default value.

DECLARE _retention bigint := coalesce((SELECT retention_days as retention_days
          FROM   retention_period
          WHERE  fulfilment_type = 'daily'), 1);