Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

like image 501
Axel Fontaine Avatar asked Jun 28 '11 14:06

Axel Fontaine


People also ask

What is the difference between schema and database in PostgreSQL?

1. A database in PostgreSQL contains the subset of schema. It contains all the schemas, records, and constraints for tables. A Schema in PostgreSQL is basically a namespace that contains all the named database objects like tables, indexes, data types, functions, stored procedures, etc.

Can we create schema inside schema?

An SQL statement defining an object to be created within the schema. Currently, only CREATE TABLE , CREATE VIEW , CREATE INDEX , CREATE SEQUENCE , CREATE TRIGGER and GRANT are accepted as clauses within CREATE SCHEMA . Other kinds of objects may be created in separate commands after the schema is created.


2 Answers

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; 
like image 42
Kiruthika kanagarajan Avatar answered Sep 28 '22 10:09

Kiruthika kanagarajan


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; 
like image 200
Berry Langerak Avatar answered Sep 28 '22 10:09

Berry Langerak