PostgreSQL schemas commands

PostgreSQL schema operations

To create a new schema, you use the CREATE SCHEMA statement. To rename a schema or change its owner, you use the ALTER SCHEMA statement. To drop a schema, you use the DROP SCHEMA statement.

select current_schema();

select current_schema();

db_intranet=> select current_schema;
 current_schema
----------------
 public
(1 ligne)

db_intranet=>

setting the search_path

SET search_path TO myschema, public;

Update the search_path

export PGPASSWORD=XXXXXXXXXXX; psql -h localhost -d db_intranet -p 5433 -U intranet -c "ALTER ROLE intranet SET search_path = intranet, public;"

Show the search path

with psql -c

✦ ❯ export PGPASSWORD=XXXXXXXXXXX; psql -U intranet -h localhost -p 5433 -c "\drds"

                   Liste des paramètres
   Rôle   | Base de données |           Réglages
----------+-----------------+------------------------------
 intranet |                 | search_path=intranet, public
(1 ligne)

with psql

export PGPASSWORD=XXXXXXXXXXX; psql -U intranet -h localhost -p 5433

psql (15.2 (Debian 15.2-1.pgdg110+1))
Connexion SSL (protocole : TLSv1.3, chiffrement : TLS_AES_256_GCM_SHA384, compression : désactivé)
Saisissez « help » pour l'aide.

db_intranet=> show search_path;

   search_path
------------------
 intranet, public
(1 ligne)