Horton
Menu

Thread

  1. How do I check for NULL

    Igor Korot <ikorot01@gmail.com> — 2025-12-09T02:39:46Z

    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?

Thank you..

  2. Re: How do I check for NULL

    David G. Johnston <david.g.johnston@gmail.com> — 2025-12-09T02:44:01Z

    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.

  3. Re: How do I check for NULL

    Ron Johnson <ronljohnsonjr@gmail.com> — 2025-12-09T02:51:55Z

    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!

  4. Re: How do I check for NULL

    Ron Johnson <ronljohnsonjr@gmail.com> — 2025-12-09T02:58:52Z

    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!

  5. Re: How do I check for NULL

    Igor Korot <ikorot01@gmail.com> — 2025-12-09T07:53:08Z

    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.
>
> David J.
>

  6. Re: How do I check for NULL

    David G. Johnston <david.g.johnston@gmail.com> — 2025-12-09T13:43:57Z

    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.

  7. Re: How do I check for NULL

    Adrian Klaver <adrian.klaver@aklaver.com> — 2025-12-09T16:09:51Z

    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

  8. How do I check for NULL

    Thiemo Kellner <thiemo@gelassene-pferde.biz> — 2025-12-09T17:14:27Z

    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

  9. How do I check for NULL

    Thiemo Kellner <thiemo@gelassene-pferde.biz> — 2025-12-09T17:16:28Z

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

  10. Re: How do I check for NULL

    David G. Johnston <david.g.johnston@gmail.com> — 2025-12-09T17:29:17Z

    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.

  11. Re: How do I check for NULL

    Juan Rodrigo Alejandro Burgos Mella <rodrigoburgosmella@gmail.com> — 2025-12-09T20:40:04Z

    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ó:

> 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?
>
> Thank you..
>
>
>

  12. Re: How do I check for NULL

    David G. Johnston <david.g.johnston@gmail.com> — 2025-12-09T21:56:24Z

    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.

  13. Re: How do I check for NULL

    Thiemo Kellner <thiemo@gelassene-pferde.biz> — 2025-12-09T22:33:13Z

    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)

  14. Re: How do I check for NULL

    Justin Swanhart <greenlion@gmail.com> — 2025-12-09T22:38:32Z

    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:

>
> 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)
>
>
>