Part 11 – Postgres: What is Schema in PostgreSQL #postgresql #postgres

What is Schema in postgresql
A PostgreSQL database cluster contains one or more named databases.
A database contains one or more named schemas.
Schema contains tables, data types, functions, and operators etc.

2/many schemas in one database
schema1 : Create a table actor;
schema2 : Create a table actor;

User can access actor which is present in schema1 and schema2 with same name.
Schemas are analogous to directories at the operating system level.

How to create a schema
CREATE SCHEMA schema1;

To create or access objects in a schema, write a qualified name consisting of the schema name and table name separated by a dot:
database.schema.table

select * from actor;.

CREATE TABLE schema1.actor
(
actor_id integer NOT NULL DEFAULT nextval(‘actor_actor_id_seq’::regclass),
first_name character varying(45) COLLATE pg_catalog.”default” NOT NULL,
last_name character varying(45) COLLATE pg_catalog.”default” NOT NULL,
last_update timestamp without time zone NOT NULL DEFAULT now(),
CONSTRAINT actor_pkey PRIMARY KEY (actor_id)
)

select * from schema1.actor;
select * from schema2.actor;
select * from actor;

To drop a schema if it’s empty (all objects in it have been dropped), use.
DROP SCHEMA schema1;
DROP SCHEMA schema1 CASCADE;

The Schema Search Path
postgres=# SHOW search_path;
search_path
—————–
“$user”, public
(1 row)

Schema with the same name as the current user is to be searched.

To put our new schema in the path, we use:
postgres# c dvdrental;
postgres# show search_path;

postgres# SET search_path TO schema1,public;

Note : Then we no longer have access to the public schema without explicit qualification.

There is nothing special about the public schema except that it exists by default

Schemas and Privileges
By default, users cannot access any objects in schemas they do not own.
by default, everyone has CREATE and USAGE privileges on the schema public.
This allows all users that are able to connect to a given database to create objects in its public.

Usage Patterns

1) issue REVOKE CREATE ON SCHEMA public FROM PUBLIC. create a schema for each user with the same name as that user.
2)Remove the public schema from the default search path by issuing ALTER ROLE ALL SET search_path = “$user”.
3)Keep the default.

Conclusion :

you should not use the public schema.