CREATE SCHEMA ik_schema; # Query with schema name Create table ik_schema.personel (…); Select * from ik_schema.personel; # database.schema.table select * from testdb.ik_schema.personel; # Delete the schema Drop schema ik_schema; #Deleting Schema with all its objects Drop schema ik_schema cascade; # Schema owned by another user CREATE SCHEMA erp_schema AUTHORIZATION erp; # Change of ownership alter schema test_schema owner to Mehmed ; # Name change alter schema test_schema rename to yeniisim ; # schema list and owners testdb=# \dn List of schemas Name | Owner -------------+---------- erp_schema | erp ik_schema | ik_role public | postgres test_schema | mehmed
It is used to group and organize objects (table, view, index, function, procedure, etc.)
according to business logic and hierarchy.
For example, separating the data of different applications such as archive, audit,
accounting, human resources can be kept in different schemas for ease of management
and security.
It is database specific, that is each database has its own schemas.
search_path
testdb=> show search_path ; search_path ----------------- “$user”, public testdb=> set search_path=’$user’, ‘ik_schema’, ‘erp_schema’ ; testdb=> show search_path ; search_path -------------------------------- “$user”, ik_schema, erp_schema testdb=> CREATE TABLE t1 (id serial4 NOT NULL,”name” text NULL) ; testdb=> \dt *.* List of relations Schema | Name | Type | Owner --------------------+-------------------------+-------+-------- erp_schema | t2 | table | ahmed ik_schema | t1 | table | ahmed testdb=> select * from t2 ; # schema ismi vermeden sorgulanabilir. id | name ----+------ (0 rows)
It can be cumbersome to type the full name of the objects (qualified names –
schema.table_isim) in SQL statements. Search_path is used to overcome this
situation.
If no schema is specified in SQL statements, objects are searched/created in public,
which is the default schema. In other words, while creating a table if no schema is
mentioned, this table is created in the public schema. The same is true for select
statements.
It is created in the first schema in the search path. In queries, it continues by
executing in order.
Examples
ALTER ROLE ahmed IN DATABASE VT_ismi SET search_path TO “$user”,erp,public; alter role ahmed set search_path = “$user”, ik_schame,public ; show search_path ; SET search_path=”$user”,public,ik_schema; alter user dataera set search_path=ik_schema ;
✦ When ik_schema is owned by ik_role
# We create user Ahmed postgres=# create role ahmed login ; # Log in with the user Ahmed and create a table in ik_schema. postgres@srv1:~$ psql -d testdb -U ahmed CREATE TABLE ik_schema.t10 (id serial4 NOT NULL,”name” text NULL) ; ERROR: permission denied for schema ik_schema LINE 1: CREATE TABLE ik_schema.t10 (id serial4 NOT NULL,”name” text ... postgres=# exit # Connect with the postgres user and grant the ik_role privilege. postgres@srv1:~$ psql postgres=# grant ik_role to ahmed ; postgres=# exit # The table is created. postgres@srv1:~$ psql -d testdb -U ahmed testdb=> CREATE TABLE ik_schema.t10 (id serial4 NOT NULL,”name” text NULL) ;
Note: We do not enter a password because we are working on the database server. To
connect from a different machine, it is necessary to assign a password to the ahmed
user and connect with the following parameters.
psql -h 10.71.6.61 -p 5344 -d testdb -U ahmed -W
# While creating create role ahmed login password = ‘sifre’” # To change or assign the existing user’s password alter role/user ahmed password ‘sifre’ ;
✦ List all tables in a schema
#testdb veritabanındaki ik_schema sının tüm tabloları testdb=# SELECT table_name FROM information_schema.tables WHERE table_schema = ‘ik_schema’ and table_catalog = ‘testdb’;
✦ Another query that returns all tables in a schema
SELECT t.table_name, t.table_type, c.relname, c.relowner, u.usename FROM information_schema.tables t JOIN pg_catalog.pg_class c ON (t.table_name = c.relname) JOIN pg_catalog.pg_user u ON (c.relowner = u.usesysid) WHERE t.table_schema=’ik_schema’;
✦ Change ownership of all tables in a schema
SELECT ‘ALTER TABLE ‘ || table_name || ‘ OWNER TO yeni_sahip; ‘ FROM information_schema.tables WHERE table_schema = ‘ik_schema’ and table_catalog = ‘testdb’;
✦ Rename all tables in a schema (prefix “Sysdata_” to table names)
SELECT ‘alter table ‘ ||c.relname|| ‘ rename to SysData_’ ||c.relname|| ‘;’ FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind ='r' AND n.nspname=’ik_schema’ ORDER BY 1;
✦ Converting column names in a schema to lowercase.
SELECT 'ALTER TABLE ' || quote_ident(c.table_schema) || ‘.’ || quote_ident(c.table_name) || ‘ RENAME “’ || c.column_name || ‘” TO ‘ || quote_ident(lower(c.column_name)) || ‘;’ As ddlsql FROM information_schema.columns As c WHERE c.table_schema= 'ik_schema' AND c.column_name <> lower(c.column_name) ORDER BY c.table_schema, c.table_name, c.column_name;
✦ Creating a read-only user in a schema
postgres=# CREATE ROLE okuyucu WITH login password ‘sifre’ # Connects to the database to grant privileges to the schema. postgres=# \c testdb GRANT USAGE ON SCHEMA erp_schema TO okuyucu; GRANT SELECT ON ALL TABLES IN SCHEMA erp_schema TO okuyucu; GRANT SELECT ON ALL SEQUENCES IN SCHEMA erp_schema TO okuyucu; postgres@srv1:~$ psql -d testdb -U okuyucu testdb=> select * from erp_schema.t2 ; id | name ----+------ (0 rows) # To automatically grant these privileges to the new created tables. alter default privileges in schema erp_schema grant select on tables to okuyucu ;
✦ Generating SQL scripts to change ownership of schemas.
SELECT DISTINCT 'GRANT' || CASE schemaname WHEN 'pg_catalog' THEN 'USAGE' ELSE 'ALL' END || ' ON SCHEMA ' || quote_ident(schemaname) || ' TO mehmed;' FROM pg_tables;