NOT NULL constraint over a set of columns

by

@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.

More Related Contents:

  1. Continuing a transaction after primary key violation error
  2. Refactor a PL/pgSQL function to return the output of various SELECT queries
  3. How to check if a table exists in a given schema
  4. How to implement a many-to-many relationship in PostgreSQL?
  5. How do I query using fields inside the new PostgreSQL JSON datatype?
  6. Optimise PostgreSQL for fast testing
  7. Transpose latest rows per user to columns
  8. How to delete duplicate rows without unique identifier
  9. Best way to check for “empty or null value”
  10. Constraint defined DEFERRABLE INITIALLY IMMEDIATE is still DEFERRED?
  11. Simulate CREATE DATABASE IF NOT EXISTS for PostgreSQL?
  12. PostgreSQL parameterized Order By / Limit in table function
  13. SQL Between clause with strings columns
  14. Insert an image in postgresql database
  15. Perform this hours of operation query in PostgreSQL
  16. What is the purpose of constraint naming
  17. Why do NULL values come first when ordering DESC in a PostgreSQL query?
  18. How do I alter the position of a column in a PostgreSQL database table?
  19. How to find a table having a specific column in postgresql
  20. Deferrable Constraints in SQL Server
  21. INSERT COMMAND :: ERROR: column “value” does not exist
  22. Use email address as primary key?
  23. SQL query to find record with ID not in another table
  24. How to show row numbers in PostgreSQL query?
  25. Grant privileges for a particular database in PostgreSQL
  26. Find duplicate rows with PostgreSQL
  27. Upsert with a transaction
  28. PostgreSQL how to create a copy of a database or schema?
  29. PostgreSQL nested INSERTs / WITHs for foreign key insertions
  30. Postgres: convert single row to multiple rows (unpivot)

Leave a Comment