[FEATURE] Support `geometry` data type for Unit Tests

[magno32]

Is this a new bug?

• I believe this is a new bug
• I have searched the existing issues, and I could not find an existing issue for this bug

Current Behavior

Probably related to #678; Generated SQL for unit tests creates a cast of geometry columns (probably any custom type) to USER-DEFINED, which is invalid.

with __dbt__cte__int_county_state__joined as (

-- Fixture for int_county_state__joined
    select cast(null as integer)              as county_id,
           cast(null as integer)              as state_id,
           cast(null as integer)              as region_id,
           cast(null as integer)              as division_id,
           cast(null as character varying)    as name,
           cast(null as character varying)    as name_county,
           cast(null as character varying)    as state_name,
           cast(null as character varying(2)) as state_abbreviation,
           cast(null as character varying)    as region_name,
           cast(null as character varying)    as division_name,
           cast(null as bigint)               as area_land,
           cast(null as bigint)               as area_water,
           cast(null as USER - DEFINED)       as center,

           cast('POINT(0,0)' as USER - DEFINED)
                                              as geometry),
     __dbt__cte__stg_us_census__county_geo_genz as (

-- Fixture for stg_us_census__county_geo_genz
         select cast(null as integer) as county_id,

                cast('POINT(0,0)' as USER - DEFINED)
                                      as geometry)
SELECT c1.*,
       ST_asText(c1.center)   AS center_wkt,
       c2.geometry            AS geometry_simplified,
       ST_asText(c1.geometry) AS geometry_wkt,
       ST_asText(c2.geometry) AS geometry_simplified_wkt
FROM __dbt__cte__int_county_state__joined c1
         LEFT JOIN __dbt__cte__stg_us_census__county_geo_genz c2
                   ON c1.county_id = c2.county_id

This affects testing both geometry columns and standard typed columns when the base tables contain geometry.

If you apply the macro suggested in #678, you get past the incorrect cast, but the mock geometry is wrapped as though its a string.

   Database Error
    parse error - invalid geometry
    LINE 20:     cast('POINT(0,0)' as geometry)

Another note if it is relevant to a fix, this also is present when using a basic test from dbt_expectations, hopefully it uses the same rendering mechanism.

data_tests:
     - dbt_expectations.expect_column_values_to_be_of_type:
            column_type: geometry

Expected Behavior

I was hesitant to raise this issue on this adapter, since it's a postgis issue, but it feels like there should be away to configure the adapter to be aware of additional data-types.

Unit-tests should be possible when non-standard columns are present.

Steps To Reproduce

1. Create a unit test for a table with inputs:

unit_tests:
  - name: test_county_data_integrity
    model: county
    given:
      - input: ref('int_county_state__joined')
        rows:
          - geometry: POINT(0,0)
      - input: ref('stg_us_census__county_geo_genz')
        rows:
          - geometry: POINT(0,0)
    expect:
      rows:
        - geometry: POINT(0,0)

2. Run dbt test
3. Tests will fail with invalid sql

Relevant log output

An error occurred during execution of unit test 'test_county_data_integrity'. There may be an error in the unit test definition: check the data types.
   Database Error
    syntax error at or near "USER"
    LINE 18: ... cast(null as bigint) as area_water, cast(null as USER-DEFIN...

Environment

- OS: mint
- Python:  3.10.12
- dbt-postgres:  1.8.2

Additional Context

No response

[JeyP91]

Hey, we are facing the same issue with our unit tests and custom enum types.

We have types created like this:
CREATE TYPE custom_type AS ENUM ('a', 'b', 'c');

Any tables, that are using this type cannot be mocked away, because the generated SQL is invalid.
Database Error syntax error at or near "USER".

If you need more information on our use case, I can also add more details and examples.
Thanks!