Closed rubenhelsloot closed 7 months ago
As a temporary workaround, I've overwritten postgres__get_columns_in_relation
like this:
-- Override get_columns_in_relation to work with materialized views
{% macro postgres__get_columns_in_relation(relation) -%}
{% call statement('get_columns_in_relation', fetch_result=True) %}
with from_table AS (
select
column_name,
data_type,
character_maximum_length,
numeric_precision,
numeric_scale
from {{ relation.information_schema('columns') }}
where table_name = '{{ relation.identifier }}'
{% if relation.schema %}
and table_schema = '{{ relation.schema }}'
{% endif %}
ORDER BY ordinal_position
),
from_matview AS (
select
attname AS column_name,
pg_catalog.format_type(
pg_attribute.atttypid,
pg_attribute.atttypmod
) AS data_type,
NULL::int AS character_maximum_length,
38 AS numeric_precision,
9 AS numeric_scale
FROM pg_attribute
JOIN pg_class on pg_attribute.attrelid = pg_class.oid
JOIN pg_namespace on pg_class.relnamespace = pg_namespace.oid
WHERE pg_attribute.attnum > 0
AND NOT pg_attribute.attisdropped
AND pg_class.relname = '{{ relation.identifier }}'
{% if relation.schema %}
AND pg_namespace.nspname = '{{ relation.schema }}'
{% endif %}
ORDER BY pg_attribute.attnum
)
select *
FROM from_table
UNION ALL (
SELECT *
FROM from_matview
)
{% endcall %}
{% set table = load_result('get_columns_in_relation').table %}
{{ return(sql_convert_columns_in_relation(table)) }}
{% endmacro %}
Thanks for reporting this @rubenhelsloot !
This looks the same as https://github.com/dbt-labs/dbt-core/issues/9419, so I'm going to close this as a duplicate.
Your workaround might work, but I'd recommend this instead for full-backwards compatibility: https://github.com/dbt-labs/dbt-core/issues/9419#issuecomment-1907067697.
Is this a new bug?
Current Behavior
When I use
dbt_utils.star()
to list the columns of a materialized view, it renders/* no columns returned from star() macro */
instead. I looked into it and found thatadapter.get_columns_in_relation
returns an empty list.I believe this is because the relation is a materialized view, and
adapter.get_columns_in_relation
only returns the columns of tables.Expected Behavior
I would expect the construct to work for materialized views as well.
Steps To Reproduce
Relevant log output
No response
Environment
Additional Context
No response