Re: Sub-query having NULL row returning FALSE result

On Wed, Jun 29, 2016 at 2:07 AM, Sridhar N Bamandlapally <
sridhar(dot)bn1(at)gmail(dot)com> wrote:

> Hi
>
> Please go through below case
>
> postgres=# CREATE TABLE emp (id INTEGER unique, ename VARCHAR);
> CREATE TABLE
> postgres=# INSERT INTO emp VALUES (null, 'aaa');
> INSERT 0 1
> postgres=# INSERT INTO emp VALUES (null, 'bbb');
> INSERT 0 1
> postgres=# INSERT INTO emp VALUES (3, 'ccc');
> INSERT 0 1
> postgres=# INSERT INTO emp VALUES (4, 'ddd');
> INSERT 0 1
> postgres=# SELECT * FROM emp ;
> id | ename
> ----+-------
> | aaa
> | bbb
> 3 | ccc
> 4 | ddd
> (4 rows)
>
> postgres=# SELECT * FROM (SELECT 5::integer id, 'eee'::varchar ename) nr;
> id | ename
> ----+-------
> 5 | eee
> (1 row)
>
> postgres=# INSERT INTO emp SELECT * FROM (SELECT 5::integer id,
> 'eee'::varchar ename) nr WHERE id NOT IN (SELECT id FROM emp);
> INSERT 0 0
> postgres=# SELECT * FROM emp ;
> id | ename
> ----+-------
> | aaa
> | bbb
> 3 | ccc
> 4 | ddd
> (4 rows)
>
> postgres=#
>
>
> The application is generating SQL-Statement to avoid exception while
> inserting
>
> The expected behavior is to INSERT row if the NEW id is not existing in
> table, but this is doing FALSE
>
> Please advise me if am doing something wrong here or any alternate
>

​Subjectively, you are allowing an ID field to be NULL. That, for me, is
wrong.​

​Given this, as a follow-up to what Tom said, you need to decide what you
wish to happen for your NULL IDs. Until you explain that behavior it is
not possible to provide valid alternatives.​

​Usually you want to use "EXISTS", not "IN"​

​
https://www.postgresql.org/docs/9.6/static/functions-subquery.html#FUNCTIONS-SUBQUERY-EXISTS

Oh, and try adding "WHERE id IS NOT NULL"​

​David J.​