title: Understanding COALESCE in postgres
body: <p>Precise question.</p>
  
  <pre><code>Table ROW
  value1 a
  value2 b    
  value3 null
  value4 d
  </code></pre>
  
  <p>Function parameters</p>
  
  <pre><code>CREATE OR REPLACE FUNCTION "GetValues"(
    "@value1"                   VARCHAR(50),
    "@value2"                   VARCHAR(50),
    "@value3"                   VARCHAR(50),
    "@value4"                   VARCHAR(50)
  )
  BEGIN
    RETURN QUERY SELECT(
        t."value1",
        t."value2",
        t."value3",
        t."value4",
    )
    FROM "table" as t
    WHERE t."value1" = COALESCE("@value1", c."value1")
    AND t."value2" = COALESCE("@value2", c."value2")
    AND t."value3" = COALESCE("@value3", c."value3")
    AND t."value4" = COALESCE("@value4", c."value4");
  END;
  </code></pre>
  
  <p>If I use the above function and only provide the following:</p>
  
  <p><code>('a', null, null, 'd')</code></p>
  
  <p>It will return <code>[]</code> even if 'a' and 'd' are found and I found that this only happens if I provide a parameter to search for something that is <code>null</code> and the value of the row is also <code>null</code>.</p>
  
  <p><strong>OLD DESCRIPTION BELOW</strong></p>
  
  <p>I have setup a get which uses <code>COALESCE</code> successfully to search by multiple or 1 parameter(s). However, if any one of those params that are not provided (so default to NULL) are actually NULL in the db because I haven't updated that field before, then it will always return an empty array, even though one of the provided params will successful match to a row in the table.</p>
  
  <p>I just want to know if I need a new system all together to complete this search or if it is just an unfortunate effect of <code>COALESCE</code>?</p>
  
  <p>Below is the relevant snippet.</p>
  
  <pre><code>  FROM "table" as t
    WHERE t."value1" = COALESCE("@value1", c."value1")
    AND t."value2" = COALESCE("@value2", c."value2")
    AND t."value3" = COALESCE("@value3", c."value3")
    AND t."value4" = COALESCE("@value4", c."value4");
  </code></pre>
  
  <p>In the above, if I provide value1 and it matches but value4 is NULL in that row, then it will return [].</p>
  
  <p>The return is a table with each of those 4 values.</p>