Обсуждение: Query with boolean parameter

Hi all
Regarding query and parameter binding in psycopg3:

cur.execute('SELECT * FROM system.app_user WHERE can_edit_views = %s', 
(True,))
<psycopg.Cursor [TUPLES_OK] [INTRANS] (host=localhost port=5433 
user=postgres database=test4) at 0x127b7f0>
cur.execute('SELECT * FROM system.app_user WHERE can_edit_views IS %s', 
(True,))
Traceback (most recent call last):
   Python Shell, prompt 10, line 1
     # Used internally for debug sandbox under external interpreter
   File "C:\Python310\Lib\site-packages\psycopg\cursor.py", line 555, in 
execute
     raise ex.with_traceback(None)
psycopg.errors.SyntaxError: syntax error at or near "$1"
LINE 1: SELECT * FROM system.app_user WHERE can_edit_views IS $1
                                                               ^
Is there any reason why the second query results in a syntax error?
I can use the first form but usually to check a boolean or null value in 
SQL the IS [true|false|null]/ IS NOT [true|false|null] operator is used.

-- 
Paolo De Stefani

> On Mar 18, 2022, at 16:56, Paolo De Stefani <paolo@paolodestefani.it> wrote:
> Is there any reason why the second query results in a syntax error?

There's not IS operator in PostgreSQL (or in SQL).  IS NULL, IS NOT NULL, IS TRUE, and IS FALSE are in effect unary
postfixoperators, so you can't construct them that way via parameter substitution. 

Il 19/03/2022 01:00 Christophe Pettus ha scritto:
>> On Mar 18, 2022, at 16:56, Paolo De Stefani <paolo@paolodestefani.it> 
>> wrote:
>> Is there any reason why the second query results in a syntax error?
> 
> There's not IS operator in PostgreSQL (or in SQL).  IS NULL, IS NOT
> NULL, IS TRUE, and IS FALSE are in effect unary postfix operators, so
> you can't construct them that way via parameter substitution.

Thanks, i see

The problem is (for me) that with psycopg2 this works:
cur.execute('SELECT * FROM system.app_user WHERE can_edit_views IS 
TRUE')
cur.execute('SELECT * FROM system.app_user WHERE can_edit_views IS %s', 
(True,))
cur.execute('SELECT * FROM system.app_user WHERE can_edit_views IS %s', 
(None,))

Switching to psycopg 3 i have to consider many more differences than i 
expected

-- 
Paolo De Stefani

> Il 19/03/2022 01:00 Christophe Pettus ha scritto:> >> On Mar 18, 2022, at 16:56, Paolo De Stefani
<paolo@paolodestefani.it>
> >> wrote:
> >> Is there any reason why the second query results in a syntax error?
> >
> > There's not IS operator in PostgreSQL (or in SQL).  IS NULL, IS NOT
> > NULL, IS TRUE, and IS FALSE are in effect unary postfix operators, so
> > you can't construct them that way via parameter substitution.

That's unexpected. Thank you for the insight, Christophe.


On Sat, 19 Mar 2022 at 13:11, Paolo De Stefani <paolo@paolodestefani.it> wrote:

> The problem is (for me) that with psycopg2 this works:
> cur.execute('SELECT * FROM system.app_user WHERE can_edit_views IS
> TRUE')
> cur.execute('SELECT * FROM system.app_user WHERE can_edit_views IS %s',
> (True,))
> cur.execute('SELECT * FROM system.app_user WHERE can_edit_views IS %s',
> (None,))
>
> Switching to psycopg 3 i have to consider many more differences than i
> expected

Maybe you can play around with IS NOT DISTINCT FROM?

https://www.postgresql.org/docs/current/functions-comparison.html

    In [1]: import psycopg
    In [2]: cnn = psycopg.connect(autocommit=True)

    In [5]: cnn.execute("create table dist (id int primary key, cond bool)")
    In [7]: cnn.cursor().executemany("insert into dist values (%s,
%s)", [(1, True), (2, False), (3, None)])

    In [9]: cnn.execute("select * from dist where cond is not distinct
from %s", [True]).fetchone()
    Out[9]: (1, True)

    In [10]: cnn.execute("select * from dist where cond is not
distinct from %s", [False]).fetchone()
    Out[10]: (2, False)

    In [11]: cnn.execute("select * from dist where cond is not
distinct from %s", [None]).fetchone()
    Out[11]: (3, None)


-- Daniele

> On Mar 19, 2022, at 05:10, Paolo De Stefani <paolo@paolodestefani.it> wrote:
> Switching to psycopg 3 i have to consider many more differences than i expected

There have been some changes in the way psycopg2 does parameter substitution, although that one is an interesting case!
You might consider using IS DISTINCT FROM as Daniele suggested, or just =, depending on how you want nulls handled. 

On 3/19/22 05:10, Paolo De Stefani wrote:
> Il 19/03/2022 01:00 Christophe Pettus ha scritto:
>>> On Mar 18, 2022, at 16:56, Paolo De Stefani <paolo@paolodestefani.it> 
>>> wrote:
>>> Is there any reason why the second query results in a syntax error?
>>
>> There's not IS operator in PostgreSQL (or in SQL).  IS NULL, IS NOT
>> NULL, IS TRUE, and IS FALSE are in effect unary postfix operators, so
>> you can't construct them that way via parameter substitution.
> 
> Thanks, i see
> 
> The problem is (for me) that with psycopg2 this works:
> cur.execute('SELECT * FROM system.app_user WHERE can_edit_views IS TRUE')
> cur.execute('SELECT * FROM system.app_user WHERE can_edit_views IS %s', 
> (True,))
> cur.execute('SELECT * FROM system.app_user WHERE can_edit_views IS %s', 
> (None,))

The only way I could get it to work:

cur.execute(sql.SQL("select 'f' IS {}").format(sql.SQL('TRUE')))

or

cur.execute(sql.SQL("select 'f' IS {}").format(sql.SQL(str(True))))

cur.fetchone() 
 

(False,)

cur.execute(sql.SQL("select 'f' IS {}").format(sql.SQL('NULL'))) 
 


cur.fetchone() 
 
                            (False,)

> 
> Switching to psycopg 3 i have to consider many more differences than i 
> expected
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Il 19/03/2022 17:35 Christophe Pettus ha scritto:
>> On Mar 19, 2022, at 05:10, Paolo De Stefani <paolo@paolodestefani.it> 
>> wrote:
>> Switching to psycopg 3 i have to consider many more differences than i 
>> expected
> 
> There have been some changes in the way psycopg2 does parameter
> substitution, although that one is an interesting case!  You might
> consider using IS DISTINCT FROM as Daniele suggested, or just =,
> depending on how you want nulls handled.

thanks to all for the suggestions i thimk i will use the '=' operator
what do you mean with 'depending on how you want nulls handled' ???

test4=# create table test (a text, b boolean);
CREATE TABLE
test4=# insert into test values ('aaa', true), ('bbb', false), ('ccc', 
null);
INSERT 0 3
test4=# select * from test;
   a  | b
-----+---
  aaa | t
  bbb | f
  ccc |
(3 rows)


test4=# select * from test where b = true;
   a  | b
-----+---
  aaa | t
(1 row)


test4=# select * from test where b is true;
   a  | b
-----+---
  aaa | t
(1 row)


test4=# select * from test where b = null;
  a | b
---+---
(0 rows)


test4=# select * from test where b is null;
   a  | b
-----+---
  ccc |
(1 row)

I will use '= True' or '= False' in psycopg cur.execute with parameter 
substitution and 'IS NULL' without parameter when i need to check the 
null value


-- 
Paolo De Stefani