I need to write an sql script that creates both a new database AND a new schema in the database I just created.
How can I do it? Can I somehow change the current database to the new one? Or can I somehow specify the database for CREATE SCHEMA?
I'm using PostgreSQL 9.0
You can connect to the database, and execute the "CREATE SCHEMA" statement. That should result in a new schema in that database. It's not as tough as you think ;) When you want to do this from a .SQL file instead, you can use the \connect command as such:
CREATE DATABASE foo;
\connect foo;
CREATE SCHEMA yourschema;
pg_dumpall and read how it does it. Or use \connect. Whichever.
\connect is a command specific to psql. Trying to execute an SQL script containing such command in another client (e.g. pgAdmin3) will not work.psql tag after my comment.Login to New-Database with new user:
postgres=> \connect newdb user1
...
You are now connected to database "newdb" as user "user1".
newdb=>
To create schema with new user "user1" in newdb:
newdb=> CREATE SCHEMA s1;
To list the schema :
SELECT * from information_schema.schemata;
Create database using --CREATE DATABASE test;
Enter to the test database using --psql -d test;
Create your schema in test database using --create schema if not exists test_schema;
