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

Postgresql – 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?

Tags:

2

Answers


  1. 0

    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);
    Login or Signup to reply.
  2. 0

    You could try using the COALESCE function like so:

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

    replace ‘default_value‘ with the actual value you want here e.g. 0.

    COALESCE is a function used to return the first non-null argument from a list of provided arguments.
    The syntax is:
    COALESCE (argument_1, argument_2, …);

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

Back To Top
Search