The problem I’m having
I don’t know how to ensure that postgres extensions are included when a dbt model is built. This could involve either or both 1) actually ensuring that CREATE EXTENSION gets called, and 2) letting dbt know that the extension will be available in subsequent model steps.
The context of why I’m trying to do this
I need to reformat (unpivot) some tables, and I found a way to do this with the postgres extension hstore which generates key-value pairs. It’s a supplied extension so it should be available in postgres and just needs to be enabled.
What I’ve already tried
In the macro I use to do the unpivoting I have the line
{% do run_query("CREATE EXTENSION IF NOT EXISTS hstore") %}
This appeared to work for me but not for a colleague that pulled my branch and ran dbt build, instead getting the (reasonable) error
postgresql error: function hstore(record) does not exist
LINE 26: EACH(hstore(<table>))
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
so it doesn’t appear that the run_query command actually got called first or if dbt recognizes that it should get called. I don’t know then why it worked on my machine.
Summary
Where and how should I call CREATE EXTENSION to actually be able to use the hstore function in dbt?