PostgreSQL EXCLUDE

Updated May 16, 2023

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.

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:

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:

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.

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:

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:

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