CODEWITHSUNDEEP
postgresqlcoalescepostgresql-9.6
Brandon
Brandon

Reputation: 1617

Understanding COALESCE in postgres

Precise question.

Table ROW
value1 a
value2 b    
value3 null
value4 d

Function parameters

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;

If I use the above function and only provide the following:

('a', null, null, 'd')

It will return [] 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 null and the value of the row is also null.

OLD DESCRIPTION BELOW

I have setup a get which uses COALESCE 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.

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 COALESCE?

Below is the relevant snippet.

  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");

In the above, if I provide value1 and it matches but value4 is NULL in that row, then it will return [].

The return is a table with each of those 4 values.

Upvotes: 0

Views: 1969

Answers (2)

S-Man
S-Man

Reputation: 23766

Should this be a simple row comparison (give out all rows which have the same values as the input parameters)?

This could simply be achieved by the row comparator (documentation):

WHERE row(t.*) IS NOT DISTINCT FROM row("@value1", "@value2", "@value3", "@value4")

demo: db<>fiddle

If NULL as function input parameter should be a wildcard then @kurkle's solution works well.

Upvotes: 2

kurkle
kurkle

Reputation: 383

You could do it like this:

  FROM test as t
  WHERE ("@value1" IS NULL OR t."value1" = "@value1")
  AND ("@value2" IS NULL OR t."value2" = "@value2")
  AND ("@value3" IS NULL OR t."value3" = "@value3")
  AND ("@value4" IS NULL OR t."value4" = "@value4");

db<>fiddle

Upvotes: 1

Related Questions