Exclude Constraint

Description

The EXCLUDE clause defines an exclusion constraint, which guarantees that if any two rows are compared on the specified column(s) or expression(s) using the specified operator(s), not all of these comparisons will return TRUE.

Click here to navigate to the PostgreSQL documentation page for this syntax.

The exclude constraint is not supported by Snowflake.

Grammar Syntax

[ CONSTRAINT constraint_name ]
EXCLUDE [ USING index_method ] ( exclude_element WITH operator [, ... ] ) index_parameters [ WHERE ( predicate ) ]
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

exclude_element in an EXCLUDE constraint is:

{ column_name | ( expression ) } [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ]

index_parameters are:

[ INCLUDE ( column_name [, ... ] ) ]
[ WITH ( storage_parameter [= value] [, ... ] ) ]
[ USING INDEX TABLESPACE tablespace_name ]

Sample Source Patterns

PostgreSQL

CREATE TABLE cinemas (
    code        char(5),
    name       varchar(40),
    EXCLUDE USING gist (name WITH &&)
);

Snowflake

CREATE TABLE cinemas (
    code        char(5),
    name       varchar(40)
-- ** MSC-ERROR - MSC-PG0009 - EXCLUDE CONSTRAINT IS NOT SUPPORTED IN SNOWFLAKE. **
--    EXCLUDE USING gist (name WITH &&)
);

Related EWIs

1. MSC-PG0009: Exclude constraint not supported.