Add option to autocreate schema if not present?

[laurentS]

If I run target-postgres with postgres_schema set to a schema that does not exist at the start of the job, the code fails with the following error:

target_postgres.exceptions.PostgresError: ('Exception writing records', InvalidSchemaName('schema "tap_csv" does not exist\nLINE 1: CREATE TABLE "tap_csv"."mytable" ();\n                     ^\n'))

I can get around this by manually creating the schema, or possibly adding the schema creation to before_run_sql, but it feels a little cumbersome, especially if I have other SQL to run before execution. Would it make sense to add a new boolean option like autocreate_schema to issue the schema creation command if it is not there? It could be False by default to maintain the current behaviour if needed.

[tayloramurphy]

I had a similar question for the Snowflake target. Andrew suggested it be included in the SQL base code in this project https://github.com/datamill-co/target-snowflake/issues/22

[LazerJesus]

it feels like schemas should be created by target-postgres. especially if its written explicitly in the config.

[andrewcstewart]

~Doesn't this target already autocreate schema? (Is this a stale issue? )~

Nevermind! It's the meltano variant that supports schema autocreation. Always confusing :/

[mvgijssel]

Is this something the community could contribute to? As this is something we're running into as well.

[r-nyq]

Is this issue dead? Would be great to have this feature