Data types that are supported in model contracts and/or unit tests

### Contributions

- [X] I have read the contribution docs, and understand what's expected of me.

### Link to the page on docs.getdbt.com requiring updates

Maybe one of these places? 🤷
- https://docs.getdbt.com/guides/adapter-creation?step=1 (for adapter maintainers)
- https://docs.getdbt.com/reference/resource-configs/postgres-configs (for caveats specific to postgres)
- https://docs.getdbt.com/docs/build/unit-tests (for caveats specific to unit testing)

### What part(s) of the page would you like to see updated?

From time-to-time, we get a question like this:

> I'm using the FOO data type with BAR database, and I'm trying to add a model contract and/or unit tests. How come it isn't working?

Here's the super-specific nerd answer 🤓:

We support whichever data types meet these two criteria:
1. the data type name naturally falls out of an `information_schema.columns` query (or equivalent), and
2. `cast(null as DATA_TYPE_NAME)` works

For all the standard types in Postgres (`int`, `varchar`, `date`, etc, etc), both of the following hold. But it doesn't for the more exotic types (`geometry`, `money`, `hstore`, etc).

From what I can tell, we can actually overcome both in many scenarios. But it requires time and effort that could be difficult to prioritize.

But sometimes we are able to prioritize it. Like `geography` and `geometry` in Snowflake [[1](https://github.com/dbt-labs/dbt-adapters/pull/165/files), [2](https://github.com/dbt-labs/dbt-snowflake/pull/896/files), [3](https://github.com/dbt-labs/dbt-snowflake/pull/969/files)].

### Additional information

_No response_