CODEWITHSUNDEEP
sqlpostgresqlpowershellautomation
DataRiver
DataRiver

Reputation: 188

PostgreSQL - Automate schema and table creation - powershell

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
);

Upvotes: 0

Views: 2000

Answers (1)

yieldsfalsehood
yieldsfalsehood

Reputation: 3085

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.

Upvotes: 1

Related Questions