Home > Software design >  What happens if I don´t define a specific schema to the database?
What happens if I don´t define a specific schema to the database?

Time:04-24

If a specific schema is not defined in a database, where are the database objects going to be stored? Is that a good or a bad thing? Why?

CodePudding user response:

Quote from the manual

In the previous sections we created tables without specifying any schema names. By default such tables (and other objects) are automatically put into a schema named “public”. Every new database contains such a schema

If no schema is defined when creating a table, the first (existing) schema that is found in the schema search path will be used to store the table.

CodePudding user response:

In psql

create database sch_test;
CREATE DATABASE

\c sch_test 
You are now connected to database "sch_test" as user "postgres".

--Show available schemas
\dn
  List of schemas
  Name  |  Owner   
-------- ----------
 public | postgres

drop schema public ;
DROP SCHEMA

\dn
List of schemas
 Name | Owner 
------ -------
(0 rows)

show search_path ;
   search_path   
-----------------
 "$user", public

create table tbl(id integer);
ERROR:  no schema has been selected to create in
LINE 1: create table tbl(id integer);

create table test.tbl(id integer);
ERROR:  schema "test" does not exist
LINE 1: create table test.tbl(id integer);

Just to show that an object may not be created if a schema does not exist. Bottom line is an object(table, function, etc) needs to be created in a schema. If there is none available for search_path to find or you specifically point at one that does not exist the object creation will fail.

  • Related