CODEWITHSUNDEEP
sqldatabaseschemapostgresql
Axel Fontaine
Axel Fontaine

Reputation: 35179

PostgreSQL: Create schema in specific database

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

Upvotes: 99

Views: 151324

Answers (3)

Praveen
Praveen

Reputation: 21

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;

Upvotes: 1

Kiruthika kanagarajan
Kiruthika kanagarajan

Reputation: 954

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;

Upvotes: 6

Berry Langerak
Berry Langerak

Reputation: 18859

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;

Upvotes: 133

Related Questions