Postgres one or the other attribute-CodePudding

One or the other attribute. For example:

CREATE TYPE example AS(
var1 TEXT,
var2 TEXT);

If they have input for var1 they can't have a value for var2.

CodePudding user response：

Use a check constraint:

ALTER TABLE example
   ADD CHECK (val1 IS NULL OR val2 IS NULL);

This allows for both columns to be NULL, but you can easily change the condition to prevent that.

CodePudding user response：

you can create the CHECK of your custom data type in the table itself:

CREATE TABLE t (
  txt example,  
  CONSTRAINT one_var_null 
    CHECK (((txt).var1 IS NULL AND (txt).var2 IS NOT NULL) OR
           ((txt).var1 IS NOT NULL AND (txt).var2 IS NULL))
);

EDIT: much more elegant alternative suggested by @a_horse_with_no_name (num_nonnulls)

CREATE TABLE t (
  txt example,  
  CONSTRAINT one_var_null 
    CHECK (num_nonnulls((txt).var1, (txt).var2) = 1)
);

Demo: db<>fiddle