CREATE SCHEMA și search_path în PostgreSQL

CREATE SCHEMA [IF NOT EXISTS] nume [AUTHORIZATION rol];
SET search_path TO schema1, schema2;
DROP SCHEMA [IF EXISTS] nume [CASCADE];

Ce face CREATE SCHEMA și search_path

Schemele împart o bază de date în zone separate: vanzari.facturi și contabilitate.facturi pot exista în același timp. Fiecare bază de date are implicit schema public.

Când scrii doar facturi, PostgreSQL caută în schemele din search_path, în ordine. Valoarea implicită este "$user", public: mai întâi o schemă cu numele utilizatorului, apoi public.

Din PostgreSQL 15, utilizatorii obișnuiți nu mai pot crea tabele în public decât dacă primesc dreptul CREATE pe schemă. Pentru fiecare aplicație este mai curat să creezi o schemă proprie.

Parametri

Parametru	Descriere
AUTHORIZATION rol	Proprietarul schemei. Dacă lipsește numele, schema primește numele rolului.
search_path	Lista de scheme în care se caută obiectele fără prefix.
CASCADE	La DROP SCHEMA: șterge și toate obiectele din schemă.

Ce returnează

CREATE SCHEMA, SET sau DROP SCHEMA.

Exemple

Exemplul 1: Schemă separată pentru contabilitate

CREATE SCHEMA contabilitate AUTHORIZATION ana;
CREATE TABLE contabilitate.facturi (numar text PRIMARY KEY, total numeric(10, 2));

SET search_path TO contabilitate, public;
SHOW search_path;

Rezultat în psql

CREATE SCHEMA
CREATE TABLE
SET
      search_path      
-----------------------
 contabilitate, public
(1 row)

Exemplul 2: search_path implicit pentru un utilizator

Setarea se aplică la conexiunile noi ale utilizatorului în baza de date magazin.

ALTER ROLE aplicatie_web IN DATABASE magazin SET search_path TO vanzari, public;

Rezultat în psql

ALTER ROLE

Exemplul 3: Ștergerea unei scheme cu tot conținutul

DROP SCHEMA contabilitate CASCADE;

Rezultat în psql

NOTICE:  drop cascades to 2 other objects
DETAIL:  drop cascades to table contabilitate.facturi
drop cascades to table contabilitate.plati
DROP SCHEMA

Greșeli frecvente

• Creezi un tabel cu același nume în două scheme și te miri că interogarea citește din cea „greșită”: câștigă prima schemă din search_path. Scrie numele complet (schema.tabel) în codul important.
• Setezi search_path cu SET și te aștepți să rămână: SET ține doar până la închiderea conexiunii. Pentru o setare permanentă folosește ALTER ROLE ... SET sau ALTER DATABASE ... SET.

Comenzi înrudite

• CREATE DATABASE / DROP DATABASE: Creează sau șterge o bază de date pe serverul PostgreSQL. O bază de date conține scheme, tabele și utilizatorii ei au drepturi separate.
• GRANT și ALTER DEFAULT PRIVILEGES: Dă unui rol drepturi pe o bază de date, o schemă, tabele sau secvențe. ALTER DEFAULT PRIVILEGES le dă automat și pe tabelele create ulterior.
• CREATE TABLE: Creează un tabel nou, cu coloane, tipuri de date și constrângeri: PRIMARY KEY, NOT NULL, UNIQUE, CHECK, DEFAULT, FOREIGN KEY.
• CREATE EXTENSION: Activează o extensie (pg_trgm, pgcrypto, vector, postgis) în baza de date curentă: tipuri, funcții și operatori noi.