Re: How do I check for NULL

On Mon, Dec 8, 2025 at 9:40 PM Igor Korot <ikorot01@gmail.com> wrote:

Hi, ALL,
Consider the following scenario:

CREATE TABLE test(a INT, b VARCHAR(256), c INT, d VARCHAR(256), /*
more fields follows*/);
CREATE UNIQUE INDEX test_x( b, c, d );

Now I try to do:

INSERT INTO test VALUES( 0, 'abc', 12345, (SELECT foo FROM bar),
/*more data follow*/);

My problem is:

The SELECT can either return data or NULL.
Everything is good when the data is returned, but the insert fails
when the NULL is returned, because the field "d" is a part of UNIQUE
INDEX.

However,, I'd like to still insert the record and I'd like to do something like:

INSERT INTO test VALUES( 0, 'abc', 12345, IF( (SELECT foo FROM bar) ==
NULL, "postgres", <select_result>), /*more data follow*/);

What would be the best way to achieve this?

https://www.postgresql.org/docs/15/sql-createindex.html section on NULLS DISTINCT says 

"Specifies whether for a unique index, null values should be considered distinct (not equal). The default is that they are distinct, so that a unique index could contain multiple null values in a column."

That seems to mean multiple rows can have NULL in column "d".