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:

select * from 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 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.

[somanyVideo]

postgres schema에대해서 유일하게 있는 동영상이다. thank. 자막 번역 감사합니다.

[sabanaar]

Thanks, very useful introduction to PostgreSQL!!!

[Winit]

Whoa very nice content ..loved it..nobody explained like you do 😃

[inclinedscorpio]

Hi! Thanks! I have a doubt. I created role with CRUD privileges and a new user and assigned role to the user. How can I revoke the modify table privileges? I don't want the user to run Alter or Drop commands. Only CRUD operations must be used. Any idea? :)

[utubetanki]

Good information. Could you advise order of videos in which we can lookup for a Beginner please?

[tamask]

Ok, but why should I use schemas? If I want to create two tables with the same table name, why not just create two separate databases? Same if I want to control user access… Why not just create two databases? **What are the use-cases where I actually have to use schemas and using two separate databases does not solve the problem?**