EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login

PostgreSQL Schema

Home » Data Science » Data Science Tutorials » PostgreSQL Tutorial » PostgreSQL Schema

postresql schema

Introduction to PostgreSQL Schema

In PostgreSQL, a schema is a named collection of database objects which contain tables, views, sequences, indexes, data types, functions, operators and other relations. These are symmetrical to operating system level directory structure, except that the PostgreSQL schemas cannot be nested.

Working of PostgreSQL Schema

  • The database schema shows the part of the logical configuration or all of a relational database.
  • Access to the schemas can be controlled depending upon the cases required.
  • The ownership of the schema is transferrable.
  • The database schema represents how the entities that make up the database relate to each other, including its views, tables, stored procedures, etc.

How to Create a PostgreSQL Schema?

To create a schema, you need to use the CREATE SCHEMA statement. You can specify the name of your choice for a schema. The CREATE SCHEMA statement will create a schema in the current database.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

Note: To execute the CREATE SCHEMA statement, the user needs to have the CREATE a special right in the current database.

Syntax #1

CREATE SCHEMA [ IF NOT EXISTS] schema_name;

Explanation: Define the schema name for the schema to be getting created after the CREATE SCHEMA clause. The name of the schema should be unique within the current database. IF NOT EXISTS is an option clause that adds a condition for creating the new schema only if it does not exist. If you try to create a new schema that already exists in the current database without using the IF NOT EXISTS clause will result in an error.

We can create a schema for a specific user as well:

Syntax #2

CREATE SCHEMA [IF NOT EXISTS] AUTHORIZATION user_name;

Explanation:

This is similar to Syntax 1 only difference because for creating a schema for the specific users, we need to specify the user_name after the AUTHORIZATION keyword, which is the same as the schema name.

You can create a schema and list of data objects in a single statement.

Syntax #3

CREATE SCHEMA schema_name
CREATE TABLE table_name1 (...)
CREATE TABLE table_name2 (...)
CREATE VIEW view_name1
SELECT select_list FROM table_name1;

Note: Each sub-command in the above syntax does not end with a semicolon (;).

Examples to Implement PostgreSQL Schema

Let’s understand the CREATE SCHEMA statement with the help of the following examples:

Example #1

Create a New Schema. Create a new schema named EduCBASchema:

Syntax:

CREATE SCHEMA IF NOT EXISTS EduCBASchema;

We can fetch all schemas from the current database using the following statements.

Code:

SELECT  *
FROM pg_catalog.pg_namespace
ORDER BY nspname;

Output: The following result will be shown after executing the above statement:

Example - 1

Example #2

Create a new schema for a specific user. Create a schema for EduCBA user:

Syntax:

CREATE SCHEMA AUTHORIZATION EduCBA;

Create a schema and its objects in a single statement. The following example uses the CREATE SCHEMA statement to create a new schema named EduCBASCM. It also creates a table named ‘Transactions’ and a view named Transactions_list that belongs to the EduCBASCM schema:

Code:

CREATE SCHEMA EduCBASCM
CREATE TABLE Transactions(
transaction_id SERIAL NOT NULL,
transaction_date DATE NOT NULL
)
CREATE VIEW Transactions_list AS
SELECT transaction_id, transaction_date
FROM Transactions
WHERE transaction_date <= CURRENT_DATE;

Output: As a result of the above statement, we will get the following statistics:

PostgreSQL Schema - 2

How to Drop PostgreSQL Schema?

The DROP SCHEMA is used to remove a schema from the current database and remove all of its objects.

Syntax:

DROP SCHEMA [IF EXISTS] schema_name [ CASCADE | RESTRICT ];

Explanation:

  • Define the schema name after DROP SCHEMA, which we want to delete
  • Specify the IF EXISTS keywords which is optional to remove a schema only if the specified schema exists in the current database.
  • Use CASCADE to remove a schema, and all of its objects and all other objects are also deleted, which depends on those objects.
  • IIf you want to remove empty schema only, then add the RESTRICT keyword.
Note: You need to be a superuser or the schema owner to execute the DROP SCHEMA statement.

We can use one line DROP SCHEMA statement for deleting multiple schemas as follows:

Syntax:

DROP SCHEMA [ IF EXISTS ] schema_name_1 [,schema_name_2,...] [CASCADE or RESTRICT];

Example:

To understand the examples for dropping a schema, we will use some of the CREATE SCHEMA section’s schemas.

  • Remove an Empty Schema: The following statement is used to remove the EduCBASchema schema:

Code:

DROP SCHEMA IF EXISTS EduCBASchema;

  • Drop Multiple Schemas: The following statement is used to remove the multiple schemas Books and Notes using a single statement:

Code:

DROP SCHEMA IF EXISTS Books, Notes;

  • To remove a non-empty schema: The following statement will drop an EduCBASCM schema.

Code:

DROP SCHEMA EduCBASCM;

The result of the above statement is as follows:

Remove an Empty Schema

  • If the deleted schema is non-empty and you want to delete the same and its objects, you should use the CASCADE option as follows:

Code:

DROP SCHEMA EduCBASCM CASCADE;

Advantages of using PostgreSQL Schema

There are various reasons why anyone should use schemas:

  • PostgreSQL schema allows you to use a database without interfering with other databases.
  • It organizes database objects like views, indexes, sequences, data types, operators, functions, and other relations into logical groups to make these objects more manageable.
  • It is used to put third-party applications into separate schemas, which leads them not to collide with the names of each-others objects.

Conclusion

We hope from this article you have learned about PostgreSQL schema and how to create and drop PostgreSQL schema. Also, we hope you got an idea about the working of PostgreSQL Schema and its advantages.

Recommended Articles

This is a guide to PostgreSQL Schema. Here we discuss how to create and drop the PostgreSQL Schema with advantages and examples with respective syntax. You can also go through our other related articles to learn more –

  1. PostgreSQL Architecture
  2. PostgreSQL Cross Join
  3. PostgreSQL Data Types
  4. PostgreSQL Alias | How to Work?
  5. Guide to PostgreSQL LIMIT

All in One Data Science Bundle (360+ Courses, 50+ projects)

360+ Online Courses

50+ projects

1500+ Hours

Verifiable Certificates

Lifetime Access

Learn More

0 Shares
Share
Tweet
Share
Primary Sidebar
PostgreSQL Tutorial
  • Advanced
    • PostgreSQL Schema
    • Postgres List Schemas
    • PostgreSQL VARCHAR
    • Array in PostgreSQL
    • PostgreSQL DDL
    • PostgreSQL List Users
    • Postgres Default User
    • Postgres add user
    • PostgreSQL log_statement
    • PostgreSQL String Functions
    • PostgreSQL Compare Strings
    • PostgreSQL Text Search
    • PostgreSQL TEXT
    • PostgreSQL String Array
    • PostgreSQL Constraints
    • PostgreSQL UNIQUE Constraint
    • PostgreSQL INTERSECT
    • PostgreSQL Like
    • Cursors in PostgreSQL
    • PostgreSQL UNION ALL
    • Indexes in PostgreSQL
    • PostgreSQL Index Types
    • PostgreSQL REINDEX
    • PostgreSQL UNIQUE Index
    • PostgreSQL Clustered Index
    • PostgreSQL DROP INDEX
    • PostgreSQL DISTINCT
    • PostgreSQL FETCH
    • PostgreSQL RAISE EXCEPTION
    • PostgreSQL Auto Increment
    • Sequence in PostgreSQL
    • Wildcards in PostgreSQL
    • PostgreSQL Subquery
    • PostgreSQL Alias
    • PostgreSQL LIMIT
    • PostgreSQL Limit Offset
    • PostgreSQL LAG()
    • PostgreSQL Table
    • Postgres Show Tables
    • PostgreSQL Describe Table
    • PostgreSQL Lock Table
    • PostgreSQL ALTER TABLE
    • Postgres Rename Table
    • Postgres DROP Table
    • PostgreSQL Functions
    • PostgreSQL Math Functions
    • PostgreSQL Window Functions
    • Aggregate Functions in PostgreSQL
    • PostgreSQL Primary Key
    • Foreign Key in PostgreSQL
    • PostgreSQL Procedures
    • PostgreSQL Stored Procedures
    • PostgreSQL Views
    • PostgreSQL Materialized Views
    • Postgres Create View
    • PostgreSQL Triggers
    • PostgreSQL DROP TRIGGER
    • PostgreSQL Date Functions
    • PostgreSQL TO_DATE()
    • PostgreSQL Timestamp
    • PostgreSQL CURRENT_TIMESTAMP()
    • PostgreSQL Notify
    • PostgreSQL RANK()
    • PostgreSQL Select
    • PostgreSQL Average
    • PostgreSQL DATE_PART()
    • PostgreSQL EXECUTE
    • PostgreSQL COALESCE
    • PostgreSQL EXTRACT()
    • PostgreSQL Sort
    • PostgreSQL TO_CHAR
    • PostgreSQL Interval
    • PostgreSQL Number Types
    • PostgreSQL ROW_NUMBER
    • Alter Column in PostgreSQL
    • PostgreSQL Identity Column
    • PostgreSQL SPLIT_PART()
    • PostgreSQL CONCAT()
    • PostgreSQL replace
    • PostgreSQL TRIM()
    • PostgreSQL MAX
    • PostgreSQL DELETE
    • PostgreSQL Float
    • PostgreSQL OID
    • PostgreSQL log
    • PostgreSQL REGEXP_MATCHES()
    • PostgreSQL MD5 
    • PostgreSQL NOW()
    • PostgreSQL RANDOM
    • PostgreSQL round
    • PostgreSQL Trunc()
    • PostgreSQL TIME
    • PostgreSQL IS NULL
    • PostgreSQL CURRENT_TIME
    • PostgreSQL MOD()
    • Postgresql Count
    • PostgreSQL Datetime
    • PostgreSQL MIN()
    • PostgreSQL age()
    • PostgreSQL enum
    • PostgreSQL OR
    • PostgreSQL Wal
    • PostgreSQL NOT IN
    • PostgreSQL SET
    • PostgreSQL Current Date
    • PostgreSQL Compare Date
    • PostgreSQL SERIAL
    • PostgreSQL Database
    • PostgreSQL Clone Database
    • PostgreSQL Copy Database
    • PostgreSQL Restore Database
    • PostgreSQL DROP DATABASE
    • PostgreSQL ALTER DATABASE
    • Postgres DROP Database
    • Postgres Dump Database
    • PostgreSQL OFFSET
    • PostgreSQL GRANT
    • PostgreSQL COMMIT
    • PostgreSQL ROLLUP
    • PostgreSQL JSON
    • EXPLAIN ANALYZE in PostgreSQL
    • PostgreSQL Temporary Table
    • PostgreSQL cluster
    • PostgreSQL Replication
    • PostgreSQL Logical Replication
    • PostgreSQL flush privileges
    • PostgreSQL Tablespaces
    • CAST in PostgreSQL
    • PostgreSQL CTE
    • hstore in PostgreSQL
    • PostgreSQL DECODE()
    • PostgreSQL Vacuum
    • PostgreSQL EXCLUDE
    • Postgres Change Password
    • Postgres Delete Cascade
    • PostgreSQL EXCEPT
    • PostgreSQL Roles
    • PostgreSQL Link
    • PostgreSQL Partition
    • PostgreSQL column does not exist
    • PostgreSQL Log Queries
    • PostgreSQL escape single quote
    • PostgreSQL Query Optimization
    • PostgreSQL Character Varying
    • PostgreSQL Transaction
    • PostgreSQL Extensions
    • PostgreSQL Import CSV
    • PostgreSQL Client
    • PostgreSQL caching
    • PostgreSQL JDBC Driver
    • PostgreSQL Interview Questions
  • Basic
    • What is PostgreSQL
    • PostgreSQL Features
    • How to Install PostgreSQL
    • PostgreSQL Versions
    • PostgreSQL Architecture
    • PostgreSQL GUI
    • PostgreSQL Variables
    • PostgreSQL Data Types
    • PostgreSQL NOT NULL
    • PostgreSQL Integer
    • PostgreSQL Boolean
    • PostgreSQL NULLIF
    • PostgreSQL Administration
    • PostgreSQL Commands
    • PostgreSQL Operators
    • PostgreSQL IN Operator
  • Control Statement
    • PostgreSQL IF Statement
    • PostgreSQL if else
    • PostgreSQL CASE Statement
    • PostgreSQL LOOP
    • PostgreSQL For Loop
    • PostgreSQL While Loop
  • Joins
    • Joins in PostgreSQL
    • PostgreSQL Inner Join
    • PostgreSQL Outer Join
    • LEFT OUTER JOIN in PostgreSQL
    • PostgreSQL FULL OUTER JOIN
    • PostgreSQL LEFT JOIN
    • PostgreSQL Full Join
    • PostgreSQL Cross Join
    • PostgreSQL NATURAL JOIN
    • PostgreSQL UPDATE JOIN
  • Queries
    • PostgreSQL Queries
    • PostgreSQL WHERE Clause
    • PostgreSQL WITH Clause
    • PostgreSQL ORDER BY
    • PostgreSQL ORDER BY Random
    • PostgreSQL GROUP BY
    • PostgreSQL group_concat
    • PostgreSQL HAVING
    • PostgreSQL Recursive Query
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

© 2020 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

*Please provide your correct email id. Login details for this Free course will be emailed to you
Book Your One Instructor : One Learner Free Class

Let’s Get Started

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

Special Offer - All in One Data Science Bundle (360+ Courses, 50+ projects) Learn More