PostgreSQL - Automate schema and table creation - powershell

Question

I am trying to automate the creation of schemas and some tables into that newly created schema. I am trying to write a script in powershell to help me achieve the same. I have been able to create the schema, however, I cannot create the tables into that schema.

I am passing the new schema to be created as a variable to powershell.

script so far (based off the solution from the following answer. StackOverFlow Solution):

$MySchema=$args[0]

$CreateSchema = 'CREATE SCHEMA \"'+$MySchema+'\"; set schema '''+$MySchema+''';'
write-host $CreateSchema
C:\PostgreSQL\9.3\bin\psql.exe -h $DBSERVER -U $DBUSER -d $DBName -w -c $CreateSchema

# To create tables
C:\PostgreSQL\9.3\bin\psql.exe -h $DBSERVER -U $DBUSER -d $DBName -w -f 'E:\automation\scripts\create-tables.sql' -v schema=$MySchema

At the execution, I see the following error:

psql:E:/automation/scripts/create-tables.sql:11: ERROR:  no schema has been selected to create in

The content of create-tables.sql is:

SET search_path TO :schema;

CREATE TABLE testing (
  id SERIAL,
  QueryDate varchar(255) NULL
);

yieldsfalsehood · Accepted Answer · 2014-01-15 20:33:48Z

1

You've got this in your first step:

$CreateSchema = 'CREATE SCHEMA \"'+$MySchema+'\"; set schema '''+$MySchema+''';'

Take out that set schema - it's erroneous and causing the schema not to be created. Then on the next step you wind up with an empty search path (because the schema never got created), which is why you get that error.

answered Jan 15, 2014 at 20:33

yieldsfalsehood

3,1051 gold badge21 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

DataRiver Over a year ago

I removed the set schema code. Still, the same error persists. psql:E:/automation/scripts/create-tables.sql:11: ERROR: no schema has been selected to create in

DataRiver Over a year ago

Anything else which I can do to get this working? To me, it seems to be a trivial thing but, i cannot seem to find a quick solution, any help is appreciated.

yieldsfalsehood Over a year ago

I think you'll need to go through step by step. First comment out the second call to psql, run your script, then check manually that the schema exists. If so, comment out the first call to psql, run your script, and check if the testing table exists in the schema. You might also want to look through the database log files, and possibly set "log_statement = all" in your configuration file to see everything that actually gets run.

DataRiver Over a year ago

My code so far creates a schema and fails with the error when I am trying to create the tables. Since this is something which HI will be running only once per client, I am not worrying too much about checking if the schema exists (atleast not for now).

yieldsfalsehood Over a year ago

If the error you get when trying to create your tables is that "no schema has been selected to create in" then you should be checking if the schema exists.

|

Collectives™ on Stack Overflow

PostgreSQL - Automate schema and table creation - powershell

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related