Support is.null/not.is.null when domain representation is used with non-nullable field

[austin-beer]

Problem

I've defined the following domain representation for a non-nullable TIMESTAMPTZ column that reports -infinity as null in the JSON representation and when filtering.

CREATE DOMAIN common."TimeValidFromType" AS TIMESTAMPTZ;

CREATE OR REPLACE FUNCTION common."TimeValidFromTypeToJson"(ts common."TimeValidFromType")
RETURNS JSON
AS $$
BEGIN
  IF ts IS NULL OR NOT isfinite(ts) THEN
    RETURN to_json(NULL::TIMESTAMPTZ);
  END IF;

  RETURN to_json(ts);
END;
$$
LANGUAGE plpgsql IMMUTABLE;

CREATE CAST (common."TimeValidFromType" AS JSON) WITH FUNCTION common."TimeValidFromTypeToJson"(common."TimeValidFromType") AS IMPLICIT;

CREATE OR REPLACE FUNCTION common."TextToTimeValidFromType"(tx TEXT)
RETURNS common."TimeValidFromType"
AS $$
BEGIN
  -- Convert an empty string as well since that's what the Supabase.Postgrest client
  -- sends when it creates a primary key filter from a null value.
  -- Convert the string 'null' as well since "is.null" will not work as a PostgREST
  -- filter and so we have to use "eq.null" instead.
  IF tx IS NULL OR tx = '' OR tx = 'null' THEN
    RETURN '-infinity'::TIMESTAMPTZ;
  END IF;

  RETURN tx::TIMESTAMPTZ;
END;
$$
LANGUAGE plpgsql IMMUTABLE;

CREATE CAST (TEXT AS common."TimeValidFromType") WITH FUNCTION common."TextToTimeValidFromType"(TEXT) AS IMPLICIT;

The issue is that clients are not able to use the is.null/not.is.null filters on this column because internally the column is never null. But the domain representation reports the field as null, and so for API consistency it would be great if is.null/not.is.null worked as expected from the client's perspective.

The workaround that I've implemented above is to convert the "null" string in filter requests so that eq.null/not.eq.null works for this field. That's obviously not ideal since it means a different syntax for this one column.

Solution

Whenever is.null/not.is.null is applied to a field that contains a TEXT domain representation conversion function, pass null into the function and use the return value of the function in the SQL IS check.

Versions

PostgreSQL: 18.0
PostgREST: v14

[taimoorzaeem]

The workaround that I've implemented above is to convert the "null" string in filter requests so that eq.null/not.eq.null works for this field. That's obviously not ideal since it means a different syntax for this one column.

I think given your specific domain logic, your current workaround is an appropriate solution.

Solution

Whenever is.null/not.is.null is applied to a field that contains a TEXT domain representation conversion function, pass null into the function and use the return value of the function in the SQL IS check.

I see two issues with this solution:

• PostgREST would need to check cast on every query. It would impact performance for a simple NULL check
• It is complex to implement: What if multiple casts exist?

I am not sure about your specific use-case but have you explored other solutions like a generated column for NULL check instead of having a cast?

[austin-beer]

Thanks for the feedback, @taimoorzaeem!

Regarding the issues you raised with the suggested solution, I'm not sure I follow.

1. Today, if a user provides an "eq" filter on a column that has a TEXT domain representation, the SQL has to pass the filter parameter to the function and use the result. Otherwise it doesn't. My proposed solution operates the same way, except for the "is" filter. The only performance impact would be the need to check the schema cache for the presence of a TEXT domain representation. Since that's already being done for "eq" filters, I'm assuming it doesn't significantly impact performance. But perhaps I'm wrong there?
2. The logic for selecting a TEXT domain representation for the "eq" filter already exists today. So that logic could be re-used for this proposed solution. I'm not sure how that handles the situation where there are multiple TEXT domain representations for the same column. But regardless of what it does, it wouldn't be new or different logic. So I wouldn't expect it to be that complex to implement.
3. I did just think of another potential issue with the proposed solution. Existing TEXT domain representation functions would need to be updated to convert the "null" TEXT string to a NULL value so that the existing is.null/is.not.null behavior continues to work for them like it does today. Since most probably don't do that today, the proposed solution would be a breaking change for them, which wouldn't be good.