'What happens if I don´t define a specific schema to the database?

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?

sqlpostgresqlschema


Solution 1:[1]

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.

Solution 2:[2]

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.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 Adrian Klaver

Related Questions