piccolo: Where JSONB column is_null produces incorrect query statement

python 3.10.1 piccolo: 0.66.0 db: Postgres

When doing a select or otherwise and comparing a JSONB columns to None or .is_null() the query string adds in a python object causing an error in the Postgres prepare statement.

Table.select(Table.id).where(Table.jsonb_column == None).run_sync()
Table.select(Table.id).where(Table.jsonb_column.is_null()).run_sync()

Emitted sql

SELECT "table"."id" FROM table WHERE "table"."jsonb_column" IS NULL'"<piccolo.columns.combination.Undefined object at 0x103eded10>"'

Error

asyncpg.exceptions.PostgresSyntaxError: syntax error at or near "NULL$1"

About this issue

  • Original URL
  • State: open
  • Created 2 years ago
  • Comments: 18 (18 by maintainers)

Most upvoted comments

@theelderbeever I think you’re right - making None always mean SQL null is more straight forward and easier to understand.