92

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

Axel Fontaine
  • 34,542
  • 16
  • 106
  • 137

3 Answers3

122

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;
Berry Langerak
  • 18,561
  • 4
  • 45
  • 58
  • 8
    I know that. But how can I do it in the same SQL file as the one that created the database with CREATE DATABASE? – Axel Fontaine Jun 28 '11 at 14:45
  • 1
    @Axel: You connect to the database. Don't know how? Simple do a `pg_dumpall` and read how it does it. Or use \connect. Whichever. – Seth Robertson Jun 28 '11 at 14:47
  • @Seth Robertson Can you provide an example in an answer so I can accept it? – Axel Fontaine Jun 28 '11 at 14:52
  • @Axel Fontaine Ah, sorry, that wasn't really clear from your question. I've updated my answer accordingly. – Berry Langerak Jun 28 '11 at 14:59
  • 20
    Bear in mind that `\connect` is a command specific to [`psql`](http://www.postgresql.org/docs/current/static/app-psql.html). Trying to execute an SQL script containing such command in another client (e.g. pgAdmin3) will not work. – Milen A. Radev Jun 28 '11 at 20:06
  • @Millen A. Radev That is very true. However, since his question is tagged with "psql", I'm doubting that is going to be an issue. But yes, \connect is psql specific. – Berry Langerak Jun 29 '11 at 08:23
  • 3
    The OP has added the `psql` tag after my comment. – Milen A. Radev Jun 30 '11 at 18:55
4

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;
Kiruthika kanagarajan
  • 824
  • 9
  • 12
0

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;

Praveen
  • 11