CODEWITHSUNDEEP
sqldatabasepostgresqlschemarelational-database
rjcf18
rjcf18

Reputation: 24

PostgreSQL create a schema under a different database using a script

So what I want to do here is to run a script while connected to a database I already had using pgAdmin3. The script contains a create role, tablespace, database and a create schema and several tables under that schema.

The problem here is that when I run the script it creates the new role, tablespace and database correctly. It also creates the schema and the tables correctly but with a problem, the schema is created under the database, from which I ran the script, instead of the newly created database. The script is more or less like this.

CREATE ROLE "new_role" ... ;

CREATE TABLESPACE "new_space"
  OWNER "new_role"
  LOCATION '/home/...';


CREATE DATABASE "new_db"
    WITH OWNER = "new_role"
            TABLESPACE = "new_space";

CREATE SCHEMA "schema" AUTHORIZATION "new_role" ;

CREATE TABLE IF NOT EXISTS "schema"."new_table"( 

...  
) TABLESPACE "new_space";...

...

I already saw a solution with a \connect foo; but that is not what I wanted, I wanted it to somehow connect within the script without running things separately and running \connect foo in the terminal.

Can anyone tell me if there is anyway to do this and help me come out with a solution to this problem?

Upvotes: 0

Views: 5832

Answers (2)

Jorge
Jorge

Reputation: 91

Using pgAdminIV:
1- right click on default database "postgres"
2- select create database, give a name f.e. "newdatabase"
3- click on "newdatabase" (to establish connection)
4- open the query tool
5- import, write or paste your code
6- run your code f.e.: CREATE SCHEMA newschema;

It works for me...

Upvotes: 0

khampson
khampson

Reputation: 15296

Use psql and split it up into two scripts . You can save the scripts in .sql files, and then run psql to connect to the DB you want to run each script against all on the same command line (with && in between each command). The two psql commands could be combined into one bash script so it's only one command that you need to run.

Something like this, if the script were named foo.sql:

psql -X -h <host> -U <user> -p <port> -f foo.sql <db_name>

The first script could have the create role, create tablespace and create database commands, connecting to the postgres db or a template DB, and the second script could have the rest of the commands.

You could also use createdb from the bash script instead of CREATE DATABASE.

Upvotes: 2

Related Questions