Adding an One-Out-Of-Two Not Null Constraint in Postgresql

NOT NULL constraint over a set of columns

@Igor is quite right and a couple of OR'ed expression are fast and simple.

For a long list of columns (a, b, c, d, e, f, g in the example), this is shorter and just as fast:

CHECK (NOT (a,b,c,d,e,f,g) IS NULL)

db<>fiddle here

_{Old sqlfiddle}

How does it work?

A more verbose form of the above would be:

CHECK (NOT ROW(a,b,c,d,e,f,g) IS NULL)

ROW is redundant syntax here.

Testing a ROW expression with IS NULL only reports TRUE if every single column is NULL - which happens to be exactly what we want to exclude.

It's not possible to simply reverse this expression with (a,b,c,d,e,f,g) IS NOT NULL, because that would test that every single column IS NOT NULL. Instead, negate the whole expression with NOT. Voilá.

More details in the manual here and here.

An expression of the form:

CHECK (COALESCE(a,b,c,d,e,f,g) IS NOT NULL)

would achieve the same, less elegantly and with a major restriction: only works for columns of matching data type, while the check on a ROW expression works with any columns.

Postgres constraint ensuring one column of many is present?

Since PostgreSQL 9.6 you have the num_nonnulls and num_nulls comparison functions that accept any number of VARIADIC arguments.

For example, this would make sure exactly one of the three columns is not null.

ALTER TABLE your_table
ADD CONSTRAINT chk_only_one_is_not_null CHECK (num_nonnulls(col1, col2, col3) = 1);

History & References

The PostgreSQL 9.6.0 Release Notes from 2016-09-29 say:

Add variadic functions num_nulls() and num_nonnulls() that count the number of their arguments that are null or non-null (Marko Tiikkaja)

On 2015-08-12, Marko Tiikkaja proposed this feature on the pgsql-hacker mailing list:

I'd like to suggest $SUBJECT for inclusion in Postgres 9.6. I'm sure everyone would've found it useful at some point in their lives, and the fact that it can't be properly implemented in any language other than C I think speaks for the fact that we as a project should provide it.

A quick and dirty proof of concept (patch attached):

=# select count_nulls(null::int, null::text, 17, 'bar');
  count_nulls
-------------
            2
(1 row)

Its natural habitat would be CHECK constraints, e.g:

  CHECK (count_nulls(a,b,c) IN (0, 3))

Avid code historians can follow more discussion from that point. :)

How to set not null constraint to columns in postgres

You can't provide a list of column in parentheses, you need to use multiple ALTER COLUMN options separated by a comma:

ALTER TABLE the_table
    ALTER COLUMN test_id set not null, 
    ALTER COLUMN type SET NOT NULL;

Composite PRIMARY KEY enforces NOT NULL constraints on involved columns

If you need to allow NULL values, use a UNIQUE constraint (or index) instead of a PRIMARY KEY (and add a surrogate PK column - I suggest a serial or IDENTITY column in Postgres 10 or later).

• Auto increment table column

A UNIQUE constraint allows columns to be NULL:

CREATE TABLE distributor (
  distributor_id GENERATED ALWAYS AS IDENTITY PRIMARY KEY
, m_id integer
, x_id integer
, UNIQUE(m_id, x_id)  -- !
-- , CONSTRAINT distributor_my_name_uni UNIQUE (m_id, x_id)  -- verbose form
);

The manual:

For the purpose of a unique constraint, null values are not considered equal, unless NULLS NOT DISTINCT is specified.

In your case, you could enter something like (1, NULL) for (m_id, x_id) any number of times without violating the constraint. Postgres never considers two NULL values equal - as per definition in the SQL standard.

If you need to treat NULL values as equal (i.e. "not distinct") to disallow such "duplicates", I see two three (since Postgres 15) options:

0. NULLS NOT DISTINCT

This option was added with Postgres 15 and allows to treat NULL values as "not distinct", so two of them conflict in a unique constraint or index. This is the most convenient option, going forward. The manual:

That means even in the presence of a unique constraint it is possible
to store duplicate rows that contain a null value in at least one of
the constrained columns. This behavior can be changed by adding the
clause NULLS NOT DISTINCT ...

Detailed instructions:

• Create unique constraint with null columns

1. Two partial indexes

In addition to the UNIQUE constraint above:

CREATE UNIQUE INDEX dist_m_uni_idx ON distributor (m_id) WHERE x_id IS NULL;
CREATE UNIQUE INDEX dist_x_uni_idx ON distributor (x_id) WHERE m_id IS NULL;

But this gets out of hands quickly with more than two columns that can be NULL. See:

• Create unique constraint with null columns

2. A multi-column UNIQUE index on expressions

Instead of the UNIQUE constraint. We need a free default value that is never present in involved columns, like -1. Add CHECK constraints to disallow it:

CREATE TABLE distributor (
   distributor serial PRIMARY KEY
 , m_id integer
 , x_id integer
 , CHECK (m_id <> -1)
 , CHECK (x_id <> -1)
);

CREATE UNIQUE INDEX distributor_uni_idx
ON distributor (COALESCE(m_id, -1), COALESCE(x_id, -1));

PostgreSQL add new not null column and fill with ids from insert statement

demo:db<>fiddle

According to the answers presented here: How can I add a column that doesn't allow nulls in a Postgresql database?, there are several ways of adding a new NOT NULL column and fill this directly.

Basicly there are 3 steps. Choose the best fitting (with or without transaction, setting a default value first and remove after, leave the NOT NULL contraint first and add afterwards, ...)

---

Step 1: Adding new column (without NOT NULL constraint, because the values of the new column values are not available at this point)

ALTER TABLE data ADD COLUMN content_id integer;

Step 2: Inserting the data into both tables in a row:

WITH inserted AS (                -- 1
    INSERT INTO content
    SELECT
        generate_series(
            (SELECT MAX(id) + 1 FROM content),
            (SELECT MAX(id) FROM content) + (SELECT COUNT(*) FROM data)
        ),
        'dummy text'
    RETURNING id
), matched AS (                   -- 2
   SELECT
       d.id AS data_id,
       i.id AS content_id
   FROM (
       SELECT
           id,
           row_number() OVER ()
       FROM data
   ) d 
   JOIN (
      SELECT
          id,
          row_number() OVER ()
      FROM inserted
   ) i ON i.row_number = d.row_number
)                                 -- 3
UPDATE data d
SET content_id = s.content_id
FROM (
    SELECT * FROM matched
) s
WHERE d.id = s.data_id;

Executing several statements one after another by using the results of the previous one can be achieved using WITH clauses (CTEs):

1. Insert data into content table: This generates an integer series starting at the MAX() + 1 value of the current content's id values and has as many records as the data table. Afterwards the new ids are returned
2. Now we need to match the current records of the data table with the new ids. So for both sides, we use row_number() window function to generate a consecutive row count for each records. Because both, the insert result and the actual data table have the same number of records, this can be used as join criterion. So we can match the id column of the data table with the new content's id values
3. This matched data can used in the final update of the new content_id column

Step 3: Add the NOT NULL constraint

ALTER TABLE data ALTER COLUMN content_id SET NOT NULL;

Concatentation of 2 Columns in PostgreSQL While Ignoring NULL Values

You need both osnrth1m and oseast1m to be not null. This is why you should use and but not or:

WHERE (osnrth1m IS NOT NULL AND oseast1m IS NOT NULL)

And the query is:

SELECT
        CONCAT_WS('_',osnrth1m,oseast1m)
    FROM postzon
    WHERE (osnrth1m IS NOT NULL AND oseast1m IS NOT NULL)
    ORDER BY RANDOM()
    LIMIT 10000;

If there a empty osnrth1m and oseast1m values which also need to be excluded then:

SELECT
            CONCAT_WS('_',osnrth1m,oseast1m)
        FROM postzon
        WHERE
            osnrth1m IS NOT NULL AND
            oseast1m IS NOT NULL AND
            osnrth1m <> '' AND
            oseast1m <> ''
        ORDER BY RANDOM()
        LIMIT 10000;

How to make sure only one column is not null in postgresql table

You can use the following check:

create table table_name 
(
  a integer, 
  b integer, 
  check ((a is null) != (b is null))
);

If there are more columns, you can use the trick with casting boolean to integer:

create table table_name 
(
  a integer, 
  b integer,
  ...
  n integer,
  check ((a is not null)::integer + (b is not null)::integer + ... + (n is not null)::integer = 1)
);

In this example only one column can be not null (it simply counts not null columns), but you can make it any number.

Composite Not Null constraint on two columns

I like using num_nonnulls() for this:

For at least one not null column:

check (num_nonnulls(col1, col2) >= 1)

For exactly one not null column:

check (num_nonnulls(col1, col2) = 1)

Liquibase has not built-in change for check constraints (at least not in the community version), so you will need a <sql> change for this:

<sql> 
  alter table the_table
    add constraint at_least_one_not_null
    check (num_nonnulls(col1, col2) >= 1)   
</sql>

How can I add a column that doesn't allow nulls in a Postgresql database?

You have to set a default value.

ALTER TABLE mytable ADD COLUMN mycolumn character varying(50) NOT NULL DEFAULT 'foo';

... some work (set real values as you want)...

ALTER TABLE mytable ALTER COLUMN mycolumn DROP DEFAULT;

---

Related Topics