Invalid type for parameter 'TO_GEOGRAPHY'-CodePudding

Why does casting

select cast(st_makepoint(-90.345929, 37.278424) as geography)

raise the following error:

SQL compilation error: invalid type [CAST(ST_MAKEPOINT(TO_DOUBLE(-90.345929), TO_DOUBLE(37.278424)) AS GEOGRAPHY)] for parameter 'TO_GEOGRAPHY'

While a seemingly more direct pass of the st_makepoint result to to_geography does not?

select to_geography(st_makepoint(-90.345929, 37.278424))

I'm fairly sure I'm stuck with the casting behavior in the dbt tool I'm using. Basically I'm trying to union a bunch of tables with this geography field, and in the compiled SQL this casting logic appears as a function of dbt's union_relations macro, and I don't seem to be able to control whether the casting occurs.

CodePudding user response：

The source for union_relations is here.

You can copy this macro into your own project (under the macros directory) and patch the source, and then call it with union_relations instead of dbt_utils.union_relations.

The offending lines are 106-113. Something like this should work fine:

{% for col_name in ordered_column_names -%}

    {%- set col = column_superset[col_name] %}
    {%- set col_type = column_override.get(col.column, col.data_type) %}
    {%- set col_name = adapter.quote(col_name) if col_name in relation_columns[relation] else 'null' %}
    {% if col_type == 'geography' %}
    to_geography({{ col_name }}) as {{ col.quoted }}
    {% else %}
    cast({{ col_name }} as {{ col_type }}) as {{ col.quoted }}
    {% endif %}
    {%- if not loop.last %},{% endif -%}

{%- endfor %}

CodePudding user response：

Because CAST doesn't support that particular combination of source and target datatypes