EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials PostgreSQL Tutorial PostgreSQL EXCLUDE
 

PostgreSQL EXCLUDE

Priya Pedamkar
Article byPriya Pedamkar

Updated May 16, 2023

PostgreSQL EXCLUDE

 

 

Introduction to PostgreSQL EXCLUDE

PostgreSQL excludes statements in PostgreSQL is used to compare any two rows from the specified column or expression by using the operator specified in PostgreSQL. At the time of excluding the column, the comparison operator will return the null or false value as output. We can also exclude table data and schema from the backup which we have taken from using pg_dump command in PostgreSQL. We exclude the specified constraint in PostgreSQL, we have also excluding the gist index from the table.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

Syntax

Below is the syntax of exclude in PostgreSQL.

1. Exclude the constraint at the time of table creation:

create table name_of_table (name_of_column1 data_type, name_of_column2 data_type,name_of_column3 data_type, …., name_of_columnN data_type) EXCLUDE USING constraint_name (name_of_column with operator, name_of_column with operator));

2. Exclude table from backup:

pg_dump –U name_of_user –W password –d name_of_database –exclude-table name_of_table > backup_file_name.sql

3. Exclude schema from backup:

pg_dump –U name_of_user –W password –d name_of_database –exclude-schema name_of_schema> backup_file_name.sql

4. Exclude table data from backup:

pg_dump –U name_of_user –W password –d name_of_database –exclude-table-data= name_of_table > backup_file_name.sql

Parameters

Below is the parameter description syntax of exclude in PostgreSQL:

  • Create table: This parameter is used in exclude statements of gist index at the time of index creation.
  • Name of the table: When creating a new table, define the table name and exclude the gist index from it.
  • Name of the column: Create a table with a specified column by creating an exclude constraint on it and name the column.
  • Data type: Define the data type of column and use exclude constraint on the same.
  • Exclude: Exclude is used to exclude the constraint from the table in PostgreSQL. We have excluding constraint from the table column in PostgreSQL.
  • Constraint name: When creating a table in PostgreSQL, we can exclude specified constraints at the time of creation.
  • Operator: We have used the operator while excluding constraints from the table.
  • Exclude table: This parameter is used to exclude a specified table from the whole database dump.
  • Pg_dump: This command is used to take a dump of the database by excluding the specified table in PostgreSQL.
  • Exclude schema: This parameter is used to exclude specified schema from the whole database dump.
  • Exclude table data: This parameter is used to exclude table data from the whole database dump.

How EXCLUDE statement work in PostgreSQL

Below is the working of Exclude statements:

We have excluded the constraint in PostgreSQL by using the exclude keyword in PostgreSQL. We have also used except operator to exclude the value from the other table in PostgreSQL. Using except we need to define the specific column name in the second table which was we have defined in the first table. To execute the exclude statement in PostgreSQL we need to have created table privileges on a table, create backup privileges or we need to have superuser privileges to execute statements.

Below is the example of Exclude statement requires to create table privileges on a table, create backup privileges or superuser privileges to execute the exclude statement in PostgreSQL.

Code:

pg_dump -U db_test -W -d testing --exclude-table stud1 > stud.sql
pg_dump -U postgres -W -d testing --exclude-table stud1 > stud.sql
echo $?

Output:

PostgreSQL EXCLUDE1

Explanation: In the above first example, we have used user as db_test, this user doesn’t have privileges of creating a table or superuser so, it will issue error while executing the exclude statement with pg_dump. In the second example, we have taken database backup using the username as Postgres, after using this user we have taken the database backup.

Examples to Implement PostgreSQL EXCLUDE

Below are the examples of the exclude statements:

Example #1: Exclude the constraint at the time of index creation

The below example shows that exclude the index constraint at the time of table creation. We have created a table name as test_exclude to exclude the constraint.

Code:

CREATE TABLE test_exclude(ID INT PRIMARY KEY NOT NULL, STUD_NAME TEXT, STUD_AGE INT, STUD_ADDRESS CHAR(50), EXCLUDE USING gist (STUD_NAME WITH =, STUD_AGE WITH <>));
\d+ test_exclude;

Output:

PostgreSQL EXCLUDE2

Example #2: Exclude table data by using except operator

Below is the example of Exclude the table data by using except operator.

We have used stud1 and stud2 table to exclude the data from the table.

PostgreSQL EXCLUDE3

Example #3: Exclude the table from a whole database backup

The below example shows that excludes the table from the whole database backup. We have excluded the stud1 table from a testing database backup.

Code:

pg_dump -U postgres -W -d testing --exclude-table stud1 > stud.sql
echo $?

Output:

table from a whole database backup

Example #4: Exclude the schema from a whole database backup

The below example show that excludes the schema from the whole database backup. We have excluded test schema from a testing database backup.

Code:

pg_dump -U postgres -W -d testing --exclude-schema test> stud.sql
echo $?

Output:

schema from a whole database backup

Advantages

Below are the advantages :

  • Using pg_dump we can exclude the specific table as well as schema from the whole database dump in PostgreSQL.
  • Using exclude statements we can also exclude the constraint.
  • We can exclude the table data from the PostgreSQL database dump by using pg_dump.
  • We have excluded the two table data by using except statements in PostgreSQL.

Recommended Articles

We hope that this EDUCBA information on “PostgreSQL EXCLUDE” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. Install PostgreSQL
  2. Array in PostgreSQL
  3. hstore in PostgreSQL
  4. PostgreSQL REINDEX

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

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

EDUCBA

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

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

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

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

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

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW