Not Null Constraint Over a Set of Columns

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.

SQL: NOT NULL constraint on a set of columns

CREATE TABLE user(
   first_name text,
   last_name text,
   ...,
   CHECK (first_name IS NOT NULL OR last_name IS NOT NULL)
)

How to add a new column on NOT NULL constraint

If the new row is supposed to be NOT NULL, add a DEFAULT clause to the column definition:

ALTER TABLE tab
ADD COLUMN Col3 INTEGER NOT null
DEFAULT 0;

Alternatively, omit the NOT NULL, fill the new column with UPDATE, then change the column to be NOT NULL:

ALTER TABLE tab
ALTER col3 SET NOT NULL;

After an UPDATE on the whole table, you should run VACUUM (FULL) tab to get rid of the bloat.

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;

Add a not null constraint to a table in which a column has a certain value in PostgreSQL

Do you realize that the constraint you're trying to add does not allow any rows with the country field other than 'ESP'? You say you want two conditions simultaneously (because you use the AND operator): each row must have country = 'ESP' AND non-null substitute_id

I believe what you wanted is

ALTER TABLE olimpic.tb_athlete
ADD CONSTRAINT soloESP CHECK(country != 'ESP' OR substitute_id IS NOT NULL);

This constraint will ensure that if country = 'ESP' then substitute_id must be non-null. For other countries both null and non-null values of substitute_id are valid.

But the above is only a guess because you provided neither your database's schema, nor meanings of the fields, nor the error text in English, nor the data stored in your database so that we could analyze what is really happening in your case. Please, consider editing the question to add the above

how to add not null constraint for a column after dropping it if the table contains data

As described in Pg docs you can do something like this

update table_name set columnname='fill null data' where columnname is null;
ALTER TABLE table_name ALTER COLUMN columnname SET NOT NULL;

ALTER TABLE table_name ALTER COLUMN columnname SET default ' ';

Add the NOT NULL constraint to a column

You wrote, "I can still insert empty strings (=NULL)," which sounds like a misunderstanding. In SQL, an empty string does not evaluate to NULL, or vice versa. Try inserting an empty string and doing SELECT from wall where token_message is NULL. You should get zero rows back. Then try doing an insert where you specify NULL (unquoted) as the value for your column, and you should get the expected error message.

If those tests work as expected, then everything is fine, and your problem is actually that you want to prevent blank strings from being inserted. Check out this question for suggestions, or just check for blank strings during validation, before the query.

Alter table and add constraint not null based on another column condition

This should work :

CONSTRAINT check_substitute_id CHECK (country != 'ESP' OR substitute_id IS NOT NULL)

demo

---

Related Topics