Migrate TestCatalogRelationTypes to the shared adapter tests framework

[mikealfare]

Problem

The TestCatalogRelationTypes tests that were created for the dbt-maintained adapters are all very similar. This could be simplified to use shared code which can be configured by the adapter maintainer for their specific adapter. This makes adapter maintainers' jobs easier and aligns with our test inheritance pattern.

Solution

• Move the body of TestCatalogRelationTypes into the shared adapter tests
• Inherit this test back into dbt-postgres
• Provide some in-line documentation of how to configure this test for adapter maintainers

Checklist

• I have read the contributing guide and understand what's expected of me
• I have run this code in development and it appears to resolve the stated issue
• This PR includes tests, or tests are not required/relevant for this PR
• This PR has no interface changes (e.g. macros, cli, logs, json artifacts, config files, adapter interface, etc) or this PR has already received feedback and approval from Product or DX
• This PR includes type annotations for new and modified functions

[codecov]

Codecov Report

All modified and coverable lines are covered by tests ✅

Comparison is base (7fddd6e) 86.50% compared to head (30d4d62) 86.44%.

Additional details and impacted files

@@            Coverage Diff             @@
##             main    #8952      +/-   ##
==========================================
- Coverage   86.50%   86.44%   -0.07%     
==========================================
  Files         179      179              
  Lines       26508    26508              
==========================================
- Hits        22932    22916      -16     
- Misses       3576     3592      +16     

Flag	Coverage Δ
integration	83.27% <ø> (-0.14%)	⬇️
unit	64.81% <ø> (ø)

Flags with carried forward coverage won't be shown. Click here to find out more.

see 3 files with indirect coverage changes

☔ View full report in Codecov by Sentry.
📢 Have feedback on the report? Share it here.

[github-actions]

Thank you for your pull request! We could not find a changelog entry for this change. For details on how to document a change, see the contributing guide.