[Unit Testing] Extend TestPostgresUnitTestingTypes to include complex types

[MichelleArk]

Is this your first time submitting a feature request?

• [X] I have read the expectations for open source contributors
• [X] I have searched the existing issues, and I could not find an existing issue for this feature
• [X] I am requesting a straightforward extension of existing dbt-spark functionality, rather than a Big Idea better suited to a discussion

Describe the feature

Per https://github.com/dbt-labs/dbt-core/issues/8423#issuecomment-1968059902, add postgres-specific input fixtures extend TestPostgresUnitTestingTypes to test complex types (at least JSON & ARRAY).

Describe alternatives you've considered

n/a

Who will this benefit?

dbt users of dbt-postgres using unit testing

Are you interested in contributing this feature?

Anything else?

Depends on https://github.com/dbt-labs/dbt-adapters/issues/113.

See dbt-bigquery for a similar example.

[bdewilde]

Hi @MichelleArk , this missing functionality has been a critical blocker in my ability to leverage the new unit testing framework for my models, most of which have one or two array-type columns. Do you have any idea how to go about implementing this, or know who might? I'd love to at least try, so I can unblock myself.

[MichelleArk]

Hey @bdewilde! There's a proposal written up with some issues as part of the spiking done here:https://github.com/dbt-labs/dbt-core/issues/8423#issuecomment-1968059902, have you had a chance to read through it?

It'll be a medium-sized lift because it'll require changes in the base unit materialization (in the dbt-adapters repo) to introduce a new strategy pattern that defaults to the current strategy (using relation-based column schemas), but also implements an alternative strategy (using adapter. get_column_schema_from_query). the new strategy would then need to be selected + tested for in dbt-postgres.

[bdewilde]

Hey @MichelleArk , thanks very much for the link / writing all that up. This may be... more intensive than I'm comfortable taking on, since I'm not particularly familiar with dbt's internals. 😮‍💨 So for now, I'll just submit a strong +1 for building out this capability, then let qualified folks prioritize it as able and interested.