Handing null primary key in postgres

Question

In our project, we are trying to combine multiple tables into a single table. The table has ukey and effective_date are PKs with not null constraint.

The structure works for below data as effective dates are different:

Ukey effective date combination:

ukey	effective_date	settle_date	price
u123	3/3/2025	3/8/2025	1.21215
u123	3/4/2025	3/9/2025	1.23

The requirement is for same ukey and effective_dates, there can be multiple settle_dates. So if I wanted to make it work for same ukey and effective_date, I will have to add settle_date as PK and make it non null. However there can be certain records where settle_date is null so adding settle_date with the data shown below won't work:

Ukey effective date settle_date combination:

ukey	effective_date	settle_date	price
u123	3/3/2025	3/8/2025	1.21215
u123	3/3/2025	3/9/2025	1.23
u456	3/3/2025

Two solutions I have thought are:

Always populate dummy value in settle_date eg 12/31/2999 making settle_date as PK and non null. And wherever its applicable during data load, my table will have actual settle_date.
Use sequence as GENERATED BY DEFAULT AS IDENTITY and use it as PK rather than settle_date. However there would be lot of INSERTS if I don't handle updates properly in the code.

Any other solution to this issue?

Can there be multiple rows with the same ukey and effective_date, if they all have no settle_date? If so, are there any other features that distinguish them? — Bergi
– Bergi, Commented Mar 3 at 14:26

Erwin Brandstetter · Accepted Answer · 2025-03-03 13:57:33Z

A UNIQUE constraint is almost as potent as a PRIMARY KEY, with the crucial difference that it allows null values, which are considered to be distinct values as per the SQL standard - by default. Since Postgres 15 this leeway can be cut by adding the NULLS NOT DISTINCT clause. "Certain records where settle_date is null" does not clarify to me whether multiple null values for the same (ukey, effective_date) should be allowed or not.

ALTER TABLE tbl ADD CONSTRAINT tbl_u_e_s_uniq UNIQUE (ukey, effective_date, settle_date);

Or:

ALTER TABLE tbl ADD CONSTRAINT tbl_u_e_s_uniq UNIQUE NULLS NOT DISTINCT (ukey, effective_date, settle_date);

fiddle

See:

Depending on undisclosed data types and predominant queries, the order of columns in the constraint may be optimized. (But the set of columns is required to enforce your requirement.)

I would probably add a surrogate PK (serial or IDENTITY column) additionally. See:

Auto increment table column

Guillaume Outters · Accepted Answer · 2025-03-03 16:58:46Z

1

A third option would be to degrade your constraint to a "simple" UNIQUE CONSTRAINT.

Just like with a primary key, you'll get:

consistency
an index
ability to foreign key to it (" foreign key must reference columns that either are a primary key or form a unique constraint")

create unique index on t(ukey, effective_date, coalesce(settle_date, '1900-01-01')); -- Personal opinion: choosing a past date is less risky than a future one; but to evacuate all risks, add a ", settle_date is null" to the index.

(see fiddle for demo)

(your question is in fact universal: see nearly same question on Reddit or for SQL Server)

edited Mar 3 at 16:58

answered Mar 3 at 8:35

Guillaume Outters

7,4961 gold badge22 silver badges28 bronze badges

2 Comments

Bergi Mar 3 at 14:30

"an index if you choose" - afaik, you get the index no matter what, it gets created implicitly when you add the constraint.

Guillaume Outters Mar 3 at 16:56

@Bergi you're right (and I, tired). Scratching my head, I'm now wondering how I could let myself imagine one second an RDBMS ensuring uniqueness without an index (by rereading the full table on each write?). I'll remove that from my answer.

Collectives™ on Stack Overflow

Handing null primary key in postgres

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related