How do I check for NULL

On Monday, December 8, 2025, Igor Korot <ikorot01@gmail.com> wrote:

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?

The “coalesce” function.

David J.

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

--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

On Mon, Dec 8, 2025 at 9:51 PM Ron Johnson <ronljohnsonjr@gmail.com> wrote:

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

It does work:

dba=# create table foo (a int, b int, c int, d int);
CREATE TABLE
dba=# create unique index i_foo_u1 on foo (a, b, d);
CREATE INDEX
dba=#
dba=# insert into foo values (1, 1, 1, 1);
INSERT 0 1
dba=# insert into foo values (2, 2, 2, null);
INSERT 0 1
dba=# insert into foo values (3, 3, 3, null);
INSERT 0 1
dba=# insert into foo values (4, 4, 4, null);
INSERT 0 1

--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

Hi, Davd,

On Mon, Dec 8, 2025 at 6:44 PM David G. Johnston
<david.g.johnston@gmail.com> wrote:

On Monday, December 8, 2025, Igor Korot <ikorot01@gmail.com> wrote:

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?

The “coalesce” function.

This is the query I use for my ODBC calls:

qry2 = L"INSERT INTO \"test\" VALUES( ?, ?, (SELECT c.oid
FROM pg_class c, pg_namespace nc WHERE nc.oid = c.relnamespace AND
c.relname = ? AND nc.nspname = ?), COALESCE((SELECT tableowner FROM
pg_tables WHERE tablename = ? AND schemaname = ?), \"postgres\"), ...)
ON CONFLICT DO NOTHING;";

Calling SQLExecute after parameter binding results in

"L"ERROR: column \"postgres\" does not exist;\nError while preparing
parameters"std::basic_string<wchar_t,std::char_traits<wchar_t>,std::allocator<wchar_t>

Thank you.

Tuesday, December 9, 2025, Igor Korot <ikorot01@gmail.com> wrote:

Hi, Davd,

On Mon, Dec 8, 2025 at 6:44 PM David G. Johnston
<david.g.johnston@gmail.com> wrote:

On Monday, December 8, 2025, Igor Korot <ikorot01@gmail.com> wrote:

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?

The “coalesce” function.

This is the query I use for my ODBC calls:

qry2 = L"INSERT INTO \"test\" VALUES( ?, ?, (SELECT c.oid
FROM pg_class c, pg_namespace nc WHERE nc.oid = c.relnamespace AND
c.relname = ? AND nc.nspname = ?), COALESCE((SELECT tableowner FROM
pg_tables WHERE tablename = ? AND schemaname = ?), \"postgres\"), ...)
ON CONFLICT DO NOTHING;";

Calling SQLExecute after parameter binding results in

"L"ERROR: column \"postgres\" does not exist;\nError while preparing
parameters"std::basic_string<wchar_t,std::char_traits<
wchar_t>,std::allocator<wchar_t>

Use single quotes for a string literal value.

David J.

On 12/8/25 23:53, Igor Korot wrote:

Hi, Davd,

On Mon, Dec 8, 2025 at 6:44 PM David G. Johnston
<david.g.johnston@gmail.com> wrote:

On Monday, December 8, 2025, Igor Korot <ikorot01@gmail.com> wrote:

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?

The “coalesce” function.

This is the query I use for my ODBC calls:

qry2 = L"INSERT INTO \"test\" VALUES( ?, ?, (SELECT c.oid
FROM pg_class c, pg_namespace nc WHERE nc.oid = c.relnamespace AND
c.relname = ? AND nc.nspname = ?), COALESCE((SELECT tableowner FROM
pg_tables WHERE tablename = ? AND schemaname = ?), \"postgres\"), ...)
ON CONFLICT DO NOTHING;";

Calling SQLExecute after parameter binding results in

"L"ERROR: column \"postgres\" does not exist;\nError while preparing
parameters"std::basic_string<wchar_t,std::char_traits<wchar_t>,std::allocator<wchar_t>

Read:

https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS

Thank you.

David J.

--
Adrian Klaver
adrian.klaver@aklaver.com

Hi

I believe there is a misconception. I feel, you meant to say, the subquery does not return any record which is not the same as returns NULL.

In any case, I suggest you to use the "insert select" construct, see examples in https://www.postgresql.org/docs/current/sql-insert.html, e.g. "NSERT INTO films SELECT * FROM tmp_films WHERE date_prod < '2004-05-07';"

Cheers

Thiemo

Btw, the exact error message could be helpful and should be provided to see misinterpretations.

On Tue, Dec 9, 2025 at 10:14 AM Thiemo Kellner <thiemo@gelassene-pferde.biz>
wrote:

I feel, you meant to say, the subquery does not return any record which is
not the same as returns NULL.

For a scalar subquery the final output of a zero-row query is the null
value.

David J.

Hi
Did you try setting a default value to the field?

Atte
JRBM

El lun, 8 dic 2025 a las 21:40, Igor Korot (<ikorot01@gmail.com>) escribió:

On Tue, Dec 9, 2025 at 1:40 PM Juan Rodrigo Alejandro Burgos Mella <
rodrigoburgosmella@gmail.com> wrote:

Did you try setting a default value to the field?

Defaults don't work if you actually intend to conditionally override them
-- or at least it requires something beyond a simple self-contained SQL
command to accomplish.

David J.

On 12/9/25 18:29, David G. Johnston wrote:

On Tue, Dec 9, 2025 at 10:14 AM Thiemo Kellner
<thiemo@gelassene-pferde.biz> wrote:

I feel, you meant to say, the subquery does not return any record
which is not the same as returns NULL.

For a scalar subquery the final output of a zero-row query is the null
value.

To me, it does not look like that (please note the empty line in the
last example). Can you point me to the documentation saying that 0 rows
is sometimes equal to 1 row?

postgres=# select * from pg_user;
 usename  | usesysid | usecreatedb | usesuper | userepl | usebypassrls
|  passwd  | valuntil | useconfig
----------+----------+-------------+----------+---------+--------------+----------+----------+-----------

 postgres |       10 | t           | t        | t       | t   |
******** |          |
(1 row)

postgres=# select usename from pg_user where false;
 usename
---------
(0 rows)

postgres=# select null as usename from pg_user;
 usename
---------

(1 row)

The key is the scalar subquery. A scalar subquery which selects no rows
returns NULL.

https://sqlfiddle.com/postgresql/online-compiler?id=e439059a-d46d-4d49-b8ab-9ff533656066

On Tue, Dec 9, 2025, 5:33 PM Thiemo Kellner <thiemo@gelassene-pferde.biz>
wrote: