Обсуждение: How do I check for NULL
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..
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
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.
>
> David J.
>
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
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ó:
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..
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.
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)