📔
Databases
  • Table of contents
  • SQL
    • Getting Started
      • PgAdmin Tool
    • Data Types
      • Arrays
      • Date/Time/Stamps
      • JSON
      • Internal Functions
      • Sequences
      • User Defined Data Types
    • Database
      • Schema
    • Table
      • Aggregation
      • Usefull Functions
      • Combining Tables
      • Constraints
      • Common Table Expression
      • GROUP BY and HAVING
      • OPERATORS
      • ORDER BY and DISTINCT
      • Views
      • WHERE Clause
    • Order of SQL Execution
    • Advance Table
      • Internals
      • Managing Tables
      • Partitioning Tables
      • Pivotal or Crosstab Tables
    • Joins
      • Cross & Natural Joins
      • Full, Multiple & Self Joins
      • Inner Join
      • Left and Right JOIN
    • Functions
      • Cursors
      • PL/pgSQL
      • Stored Procedures
      • Triggers
        • More on Triggers
    • Indexing
      • Custom Indexes
      • GIN Index
      • Indexes
      • SQL
      • Unique Index
    • Summarization
      • SubQueries
      • Window
  • MongoDB
    • Mongo Administration
    • MongoDB Aggregation
    • MQL
  • Redis
  • Cassandra
    • CQL
    • Data Modelling
      • Advance Data Modelling
    • Cassandra Administration
    • Cassandra Features
Powered by GitBook
On this page

Was this helpful?

  1. SQL
  2. Database

Schema

  • PostgreSQL schema's should be unique and different from each other

  • Allows you to organise database objects

  • Schema allow multiple users to interact with one database without interfering with each other

  • Allow access and limit database objects to be accessed by the user.

-- syntax to create new schema
CREATE SCHEMA sales;

-- syntax to create new schema
CREATE SCHEMA hr;

-- syntax to rename existing schema
ALTER SCHEMA sales RENAME TO marketing;

-- drop schema, DO THIS CAREFULLY !!
DROP SCHEMA hr;

-- specify the schema for the table explicitly
select * from hr.public.jobs;

-- creating a sample table
CREATE TABLE temporders ( id SERIAL PRIMARY KEY );

-- moving the table from one schema to another
ALTER TABLE public.temporders SET SCHEMA marketing;

-- show current schema
select current_schema();

-- get default search path where the query will start looking
show search_path;
   search_path   
-----------------
 "$user", public

-- order to search path is important 
SET search_path to '$user', marketing, public;

PG_CATALOG

  • PostgreSQL stores the metadata information about the database and cluster in the schema pg_catalog.

  • This information is partially used by PostgreSQL itself to keep track things itself, but it also presented so external people/processes can understand whats inside the database.

  • pg_catalog schema contains system tables and all the built-in types, functions and operators.

  • pg_catalog is effectively part of the search path. Although if it is not named explicitly in the path then it is implicitly searched before searching the path's schema.

select * from information_schema.schemata;

 catalog_name |    schema_name     | schema_owner 
--------------+--------------------+--------------
 learning     | information_schema | postgres
 learning     | public             | postgres
 learning     | pg_catalog         | postgres
 learning     | pg_toast           | postgres
PreviousDatabaseNextTable

Last updated 3 years ago

Was this helpful?