Unable to query/filter on start_datetime property after migrating from 0.8.6 to 0.9.6

[bmcandr]

I recently migrated from 0.8.6 to 0.9.6. Prior to this migration queries and filters on the start_datetime property worked as expected e.g., {"start_datetime": {"eq": "2026-01-01T00:00:00Z"}} returned matches.

After migration, I'm seeing the following error in the logs of our stac-fastapi-pgstac server on searches that include queries/filters on the start_datetime property:

asyncpg.exceptions.UndefinedFunctionError: function to_text(timestamp with time zone) does not exist
HINT: No function matches the given name and argument types. You might need to add explicit type casts.

On the other hand, queries on the end_datetime field work as expected so it seems start_datetime does not get the same special treatment(?). Is this expected or a regression? I was unable to find any mention of a change that would have this effect.

---

FWIW, I was able to replicate with stac-fastapi-pgstac @ df1de10 using docker compose after a fresh clone so it does not appear to be due to an incomplete migration or other issue in our DB.

Happy to provide any other info that might be helpful!

[bmcandr]

Going to try updating queryables as described in the docs.

Update: this did not work. In the clean local instance, I added a start_datetime queryable. The /queryables endpoint includes:

{
  "start_datetime": {
    "type": "string",
    "title": "Start datetime",
    "format": "date-time",
    "pattern": "(\\+00:00|Z)$",
    "description": "Start datetime"
  }
}

select * from pgstac.queryables includes the record:

| id | name           | collection_ids | definition                                                                                                                         | property_path | property_wrapper | property_index_type |
|----|----------------|----------------|------------------------------------------------------------------------------------------------------------------------------------|---------------|------------------|---------------------|
| 4  | start_datetime | [null]         | {"type": "string", "title": "Start datetime", "format": "date-time", "pattern": "(\\+00:00\|Z)$", "description": "Start datetime"} | [null]        | to_tstz          | BTREE               |

But the same error appears on searches that query on start_datetime

[bmcandr]

I used Claude to help me investigate and generate a patch to apply locally (see collapsible section below).

Claude's root cause analysis:

Root cause: #329 (0.9.2) made queryable() remap start_datetime to the instantiated datetime column, but the two hardcoded "instantiated column" lists inside cql2_query() were never updated to include it (they list id, datetime, geometry, end_datetime, collection). As a result start_datetime is treated as a generic queryable: with additional_properties = true it gets to_text-wrapped, producing to_text(datetime) <op> '...'::timestamptz → a text vs timestamptz type-mismatch error; with additional_properties = false it raises Term start_datetime is not found in queryables. end_datetime is unaffected because it appears in both lists.

The patch [see collapsible section below] makes three changes (each marked -- PATCH):

• Adds start_datetime to the strict-mode queryables validation skip-list (fixes the "not found in queryables" exception).
• Adds start_datetime to the native-column check so it compares natively (wrapper := NULL) instead of being to_text-wrapped.
• Resolves the leaf property through queryable().path, so start_datetime emits the datetime column rather than the nonexistent "start_datetime" column. id/datetime/end_datetime/collection are unchanged since their path is the column name itself.

Net effect: start_datetime queries the datetime column, matching the documented behavior that pgstac instantiates an item's start into datetime.

Patch

-- ============================================================================
-- Hotfix: restore start_datetime query/filter support on pgstac 0.9.6
-- ============================================================================
-- Regression introduced in 0.9.2 (commit de45386, PR #329): queryable() began
-- remapping start_datetime -> the instantiated `datetime` column, but the two
-- hardcoded "instantiated column" lists in cql2_query() were never updated to
-- match. Effects under additional_properties = true:
--   * a CQL2 comparison on start_datetime gets to_text-wrapped, producing
--     `to_text(datetime) <op> '...'::timestamptz` -> text vs timestamptz
--     type mismatch error.
-- (end_datetime is unaffected because it is present in both lists.)
--
-- This patch is a single CREATE OR REPLACE of cql2_query with three changes,
-- marked `-- PATCH` below. It is idempotent and touches no tables/data/schema.
--
-- Apply as the pgstac schema owner (pgstac_admin):
--   psql "$DSN" -v ON_ERROR_STOP=1 -f patch-start_datetime-0.9.6.sql
--
-- Reversible: re-running the original 0.9.6 cql2_query definition restores
-- prior behavior. The next forward migration that ships an upstream fix will
-- overwrite this function cleanly.
-- ============================================================================

SET search_path = pgstac, public;

CREATE OR REPLACE FUNCTION cql2_query(j jsonb, wrapper text DEFAULT NULL) RETURNS text AS $$
#variable_conflict use_variable
DECLARE
    args jsonb := j->'args';
    arg jsonb;
    op text := lower(j->>'op');
    cql2op RECORD;
    literal text;
    _wrapper text;
    leftarg text;
    rightarg text;
    prop text;
    extra_props bool := pgstac.additional_properties();
BEGIN
    IF j IS NULL OR (op IS NOT NULL AND args IS NULL) THEN
        RETURN NULL;
    END IF;
    RAISE NOTICE 'CQL2_QUERY: %', j;

    -- check if all properties are represented in the queryables
    IF NOT extra_props THEN
        FOR prop IN
            SELECT DISTINCT p->>0
            FROM jsonb_path_query(j, 'strict $.**.property') p
            -- PATCH: start_datetime is an instantiated column (maps to datetime)
            WHERE p->>0 NOT IN ('id', 'datetime', 'start_datetime', 'geometry', 'end_datetime', 'collection')
        LOOP
            IF (queryable(prop)).nulled_wrapper IS NULL THEN
                RAISE EXCEPTION 'Term % is not found in queryables.', prop;
            END IF;
        END LOOP;
    END IF;

    IF j ? 'filter' THEN
        RETURN cql2_query(j->'filter');
    END IF;

    IF j ? 'upper' THEN
        RETURN  cql2_query(jsonb_build_object('op', 'upper', 'args', j->'upper'));
    END IF;

    IF j ? 'lower' THEN
        RETURN  cql2_query(jsonb_build_object('op', 'lower', 'args', j->'lower'));
    END IF;

    -- Temporal Query
    IF op ilike 't_%' or op = 'anyinteracts' THEN
        RETURN temporal_op_query(op, args);
    END IF;

    -- If property is a timestamp convert it to text to use with
    -- general operators
    IF j ? 'timestamp' THEN
        RETURN format('%L::timestamptz', to_tstz(j->'timestamp'));
    END IF;
    IF j ? 'interval' THEN
        RAISE EXCEPTION 'Please use temporal operators when using intervals.';
        RETURN NONE;
    END IF;

    -- Spatial Query
    IF op ilike 's_%' or op = 'intersects' THEN
        RETURN spatial_op_query(op, args);
    END IF;

    IF op IN ('a_equals','a_contains','a_contained_by','a_overlaps') THEN
        IF args->0 ? 'property' THEN
            leftarg := format('to_text_array(%s)', (queryable(args->0->>'property')).path);
        END IF;
        IF args->1 ? 'property' THEN
            rightarg := format('to_text_array(%s)', (queryable(args->1->>'property')).path);
        END IF;
        RETURN FORMAT(
            '%s %s %s',
            COALESCE(leftarg, quote_literal(to_text_array(args->0))),
            CASE op
                WHEN 'a_equals' THEN '='
                WHEN 'a_contains' THEN '@>'
                WHEN 'a_contained_by' THEN '<@'
                WHEN 'a_overlaps' THEN '&&'
            END,
            COALESCE(rightarg, quote_literal(to_text_array(args->1)))
        );
    END IF;

    IF op = 'in' THEN
        RAISE NOTICE 'IN : % % %', args, jsonb_build_array(args->0), args->1;
        args := jsonb_build_array(args->0) || (args->1);
        RAISE NOTICE 'IN2 : %', args;
    END IF;



    IF op = 'between' THEN
        args = jsonb_build_array(
            args->0,
            args->1,
            args->2
        );
    END IF;

    -- Make sure that args is an array and run cql2_query on
    -- each element of the array
    RAISE NOTICE 'ARGS PRE: %', args;
    IF j ? 'args' THEN
        IF jsonb_typeof(args) != 'array' THEN
            args := jsonb_build_array(args);
        END IF;

        -- PATCH: treat start_datetime like the other instantiated columns so it
        -- is compared natively (wrapper := NULL) instead of being to_text-wrapped
        IF jsonb_path_exists(args, '$[*] ? (@.property == "id" || @.property == "datetime" || @.property == "start_datetime" || @.property == "end_datetime" || @.property == "collection")') THEN
            wrapper := NULL;
        ELSE
            -- if any of the arguments are a property, try to get the property_wrapper
            FOR arg IN SELECT jsonb_path_query(args, '$[*] ? (@.property != null)') LOOP
                RAISE NOTICE 'Arg: %', arg;
                wrapper := (queryable(arg->>'property')).nulled_wrapper;
                RAISE NOTICE 'Property: %, Wrapper: %', arg, wrapper;
                IF wrapper IS NOT NULL THEN
                    EXIT;
                END IF;
            END LOOP;

            -- if the property was not in queryables, see if any args were numbers
            IF
                wrapper IS NULL
                AND jsonb_path_exists(args, '$[*] ? (@.type()=="number")')
            THEN
                wrapper := 'to_float';
            END IF;
            wrapper := coalesce(wrapper, 'to_text');
        END IF;

        SELECT jsonb_agg(cql2_query(a, wrapper))
            INTO args
        FROM jsonb_array_elements(args) a;
    END IF;
    RAISE NOTICE 'ARGS: %', args;

    IF op IN ('and', 'or') THEN
        RETURN
            format(
                '(%s)',
                array_to_string(to_text_array(args), format(' %s ', upper(op)))
            );
    END IF;

    IF op = 'in' THEN
        RAISE NOTICE 'IN --  % %', args->0, to_text(args->0);
        RETURN format(
            '%s IN (%s)',
            to_text(args->0),
            array_to_string((to_text_array(args))[2:], ',')
        );
    END IF;

    -- Look up template from cql2_ops
    IF j ? 'op' THEN
        SELECT * INTO cql2op FROM cql2_ops WHERE  cql2_ops.op ilike op;
        IF FOUND THEN
            -- If specific index set in queryables for a property cast other arguments to that type

            RETURN format(
                cql2op.template,
                VARIADIC (to_text_array(args))
            );
        ELSE
            RAISE EXCEPTION 'Operator % Not Supported.', op;
        END IF;
    END IF;


    IF wrapper IS NOT NULL THEN
        RAISE NOTICE 'Wrapping % with %', j, wrapper;
        IF j ? 'property' THEN
            RETURN format('%I(%s)', wrapper, (queryable(j->>'property')).path);
        ELSE
            RETURN format('%I(%L)', wrapper, j);
        END IF;
    ELSIF j ? 'property' THEN
        -- PATCH: resolve through queryable().path so start_datetime emits the
        -- `datetime` column rather than the nonexistent `"start_datetime"` column.
        -- For id/datetime/end_datetime/collection the path is the column name
        -- itself, so quoting/behavior is unchanged for those.
        RETURN quote_ident((queryable(j->>'property')).path);
    END IF;

    RETURN quote_literal(to_text(j));
END;
$$ LANGUAGE PLPGSQL STABLE;

I applied this to a local instance running in Docker to test and it restored the ability to query on start_datetime. I then applied this to my prod DB and confirmed it works there as well.