'Postgres one or the other attribute

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.

sqlpostgresql


Solution 1:[1]

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.

Solution 2:[2]

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

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Laurenz Albe
Solution 2

Related Questions