Subject: Query with boolean parameter

From: Paolo De Stefani <[email-masked]>
Date: Sat, 19 Mar 2022 00:56:28 +0100

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

---

From: Christophe Pettus <[email-masked]>
Date: Fri, 18 Mar 2022 17:00:20 -0700

> On Mar 18, 2022, at 16:56, Paolo De Stefani <[email-masked]> wrote:

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.

---

From: Paolo De Stefani <[email-masked]>
Date: Sat, 19 Mar 2022 13:10:54 +0100

Il 19/03/2022 01:00 Christophe Pettus ha scritto:
> 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

---

From: Daniele Varrazzo <[email-masked]>
Date: Sat, 19 Mar 2022 14:43:19 +0100

> Il 19/03/2022 01:00 Christophe Pettus ha scritto:> >> On Mar 18, 2022, at 16:56, Paolo De Stefani <[email-masked]>
> >> wrote:

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

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

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

---

From: Christophe Pettus <[email-masked]>
Date: Sat, 19 Mar 2022 09:35:08 -0700

> On Mar 19, 2022, at 05:10, Paolo De Stefani <[email-masked]> 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.

---

From: Adrian Klaver <[email-masked]>
Date: Sat, 19 Mar 2022 10:34:32 -0700

On 3/19/22 05:10, Paolo De Stefani wrote:
> cur.execute('SELECT * FROM system.app_user WHERE can_edit_views IS TRUE')

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

-- 
Adrian Klaver
[email-masked]

---

From: Paolo De Stefani <[email-masked]>
Date: Sat, 19 Mar 2022 20:30:32 +0100

Il 19/03/2022 17:35 Christophe Pettus ha scritto:
> 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