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:
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.