union_relations macro unable to union relations with ARRAY on Postgres

pcreux commented 7 months ago

Describe the bug

Calling union_relations on relations with an array column generates invalid SQL:

  syntax error at or near "ARRAY"
  cast("my_array" as ARRAY) as "my_array"

Steps to reproduce

Create a table with an array of integers or strings:

CREATE TABLE my_table(my_array int[]);

Perform an union_relations on that table:

    {% set relations = [
      api.Relation.create(schema='public', identifier='my_table')
      ]
    %}
   {{ dbt_utils.union_relations(relations) }

Expected results

Valid SQL:

--- ...
cast(my_array as int[]) as my_array,
--- ...

Actual results

Invalid SQL:

--- ...
cast(my_array as ARRAY) as my_array,
--- ...

System information

The contents of your packages.yml file:

packages:
  - package: dbt-labs/dbt_utils
    version: 1.1.1

Which database are you using dbt with?

[x] postgres

The output of dbt --version:

Core:
  - installed: 1.7.2
  - latest:    1.7.3 - Update available!

  Your version of dbt-core is out of date!
  You can find instructions for upgrading here:
  https://docs.getdbt.com/docs/installation

Plugins:
  - bigquery: 1.7.2 - Up to date!
  - postgres: 1.7.2 - Update available!

Are you interested in contributing the fix?

The code casts using dbt.type_string() or col.data_type. I don't know if there's another attribute that would return the_type[] instead of ARRAY.