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

Question

I'm trying to setup a table and add some constraints to it. I was planning on using partial indexes to add constraints to create some composite keys, but ran into the problem of handling NULL values. We have a situation where we want to make sure that in a table only one of two columns is populated for a given row, and that the populated value is unique. I'm trying to figure out how to do this, but I'm having a tough time. Perhaps something like this:

CREATE INDEX foo_idx_a ON foo (colA) WHERE colB is NULL CREATE INDEX foo_idx_b ON foo (colB) WHERE colA is NULL

Would this work? Additionally, is there a good way to expand this to a larger number of columns?

This can be done with a trigger, but it may be able to be done more elegantly with a schema change. Could you provide a little more context as to what's in the columns? Are they all similar types of values? — Schwern
– Schwern, Commented Jul 23, 2020 at 18:58
The aren't necessarily the similar types. I'm trying to build a generic system that can generate these sort of constraints from a schema. — caffein
– caffein, Commented Jul 23, 2020 at 22:56

user330315 · Accepted Answer · 2020-07-23 20:26:53Z

Another way to write this constraint is to use the num_nonulls() function:

create table table_name ( a integer, b integer, check ( num_nonnulls(a,b) = 1) );

This is especially useful if you have more columns:

create table table_name ( a integer, b integer, c integer, d integer, check ( num_nonnulls(a,b,c,d) = 1) );

This is more elegant than my version, I didn't know postgres has that

Andronicus · Accepted Answer · 2020-07-23 19:06:37Z

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.

Schwern · Accepted Answer · 2020-07-23 19:28:56Z

One can do this with an insert/update trigger or checks, but having to do so indicates it could be done better. Constraints exist to give you certainty about your data so you don't have to be constantly checking if the data is valid. If one or the other is not null, you have to do the checks in your queries.

This is better solved with table inheritance and views.

Let's say you have (American) clients. Some are businesses and some are individuals. Everyone needs a Taxpayer Identification Number which can be one of several things such as a either a Social Security Number or Employer Identification Number.

create table generic_clients ( id bigserial primary key, name text not null ); create table individual_clients ( ssn numeric(9) not null ) inherits(generic_clients); create table business_clients ( ein numeric(9) not null ) inherits(generic_clients);

SSN and EIN are both Taxpayer Identification Numbers and you can make a view which will treat both the same.

create view clients as select id, name, ssn as tin from individual_clients union select id, name, ein as tin from business_clients;

Now you can query clients.tin or if you specifically want businesses you query business_clients.ein and for individuals individual_clients.ssn. And you can see how the inherited tables can be expanded to accommodate more divergent information between types of clients.

Collectives™ on Stack Overflow

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

3 Answers 3

1 Comment

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Related