[Bug] Snapshots and incremental models cannot add array columns

[tweavers]

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

Adding an array-type column to an existing snapshot or incremental model will result in the following syntax error.

alter table "postgres"."dbt_teddy_snapshots"."test_model_snapshot" add column "some_array" ARRAY;
Postgres adapter: Postgres error: syntax error at or near "ARRAY"

Example

# example_model.sql (before)
select
  1::numeric as col_a,
  2::int as col_b,
  'some_text' as col_c

# example_model.sql (after)
select
  1::numeric as col_a,
  2::int as col_b,
  'some_text' as col_c
  null::text[] as some_array

Expected Behavior

Alter table command should look like something like the following:
alter table "postgres"."dbt_snapshots"."example_model_snapshot" add column "some_array" text[];

Steps To Reproduce

1. Build above example_model with a downstream snapshot and incremental model
2. Add an array column to example_model
3. Build example_model and the downstream incremental models and snapshots

Relevant log output

14:41:28.002733 [debug] [Thread-1 (]: SQL status: SELECT 8 in 0.003 seconds
14:41:28.005418 [debug] [Thread-1 (]: Using postgres connection "snapshot.thymecare.test_model_snapshot"
14:41:28.005658 [debug] [Thread-1 (]: On snapshot.thymecare.test_model_snapshot: /* {"app": "dbt", "dbt_version": "1.8.6", "profile_name": "postgres", "target_name": "local", "node_id": "snapshot.thymecare.test_model_snapshot"} */

      alter table "postgres"."dbt_snapshots"."test_model_snapshot" add column "some_array" ARRAY;
    
14:41:28.009511 [debug] [Thread-1 (]: Postgres adapter: Postgres error: syntax error at or near "ARRAY"
LINE 3: ...pshots"."test_model_snapshot" add column "some_array" ARRAY;

Environment

- OS: Sequoia 15.0.1
- Python: 3.11.4
- dbt-postgres: 1.8.2

Additional Context

No response

[tweavers]

bump on this

[amychen1776]

@tweavers could you help me better understand the required syntax? Given your sample model, should dbt not try to explicitly call out the data type ARRAY and that's where it's going wrong?

[morsapaes]

AFAICT, the issue is that when there is an array type, the get_columns_in_relation macro fetches the data_type in information_schema.columns, which for arrays contains the keyword ARRAY instead of the actual data type (as mentioned in the PostgreSQL documentation). To handle this, the query used in the macro should probably use udt_name::regtype or join with the information_schema.element_types view.

postgres=# SELECT column_name, data_type, udt_name::regtype
FROM information_schema.columns
WHERE table_schema = 'public'
  AND table_name='example_model_snapshot';
  column_name   |          data_type          |          udt_name
----------------+-----------------------------+-----------------------------
 col_a          | numeric                     | numeric
 col_b          | integer                     | integer
 col_c          | text                        | text
 dbt_scd_id     | text                        | text
 dbt_updated_at | timestamp without time zone | timestamp without time zone
 dbt_valid_from | timestamp without time zone | timestamp without time zone
 dbt_valid_to   | timestamp without time zone | timestamp without time zone
 some_array     | ARRAY                       | text[]
(8 rows)

-- This is the DDL the adapter currently generates
postgres=# alter table "postgres"."public"."example_model_snapshot" add column "some_array" ARRAY;
ERROR:  syntax error at or near "ARRAY"
LINE 1: ...lic"."example_model_snapshot" add column "some_array" ARRAY;

-- This is the DDL the adapter should generate
postgres=# alter table "postgres"."public"."example_model_snapshot" add column "some_array" text[];
ALTER TABLE

I can put up a PR after taking a more careful look into the implications of such a change, if no one else has picked this up yet. cc @mikealfare

[morsapaes]

Any changes to that macro potentially need to also cover user-defined types, since these seem to not work correctly with the current implementation either (as described in #7459, #678).