I have a sql script that creates a bunch of tables in a temporary schema name in Redshift. I don't want to repeat the schema name a bunch of times, so I would like to do something like the following at the top of the script:

use long_external_schema_name;

My understanding is that Redshift (inheriting from Postgres), you would do:

set search_path to '$user', public, long_external_schema_name;

However, I get the following error:

ERROR:  External schema "long_external_schema_name" cannot be set in search_path

Because, it is an external schema.


Is there any equivalent way that I could stay DRY and write the external schema name only once while I create a bunch of tables in it?

More Context

Note, I know I have lots of options in bash (arguments, sed, vim, etc) to replace the schema name in the script, but I'm trying to do something more native to Redshift / psql.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.