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 exclud 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 exclud in PostgreSQL:
- Create table: This parameter is used in exclude statements of gist index at the time of index creation.
- Name of the table: Table name is defined as creating a new table and defined the name to the same table at that table exclude the gist index from the same.
- Name of the column: Column name is defined as to create a table with a specified column by creating exclude constraint on the same.
- 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: This is defined as we have excluding specified constraints at the time of table creation in PostgreSQL.
- Operator: We have used the operator while excluding constraints from the table.
- Pg_dump: This command is used to take a dump of the database by excluding the specified table in PostgreSQL.
- Exclude table: This parameter is used to exclude a specified table from the whole database dump.
- 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 in PostgreSQL.
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 of PostgreSQL EXCLUDE
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 in PostgreSQL.
- Using pg_dump we can exclude the table data from the whole database dump in PostgreSQL.
- We have excluded the two table data by using except statements in PostgreSQL.
Recommended Articles
This is a guide to PostgreSQL EXCLUDE. Here we discuss an introduction to PostgreSQL EXCLUDE, syntax, how does it work, advantages and query examples. You can also go through our other related articles to learn more –
