From 43cc0613b25fb906eeecbf21217ec76c7adacb29 Mon Sep 17 00:00:00 2001
From: =?utf8?q?S=C3=B6ren=20Oldag?= <soeren.oldag@kiwi.ki>
Date: Thu, 26 Oct 2023 09:17:41 -0400
Subject: [PATCH] Add bind casts for BIT on asyncpg
MIME-Version: 1.0
Content-Type: text/plain; charset=utf8
Content-Transfer-Encoding: 8bit

Fixed SQL handling for "insertmanyvalues" when using the
:class:`.postgresql.BIT` datatype with the asyncpg backend.  The
:class:`.postgresql.BIT` on asyncpg apparently requires the use of an
asyncpg-specific `BitString` type which is currently exposed when using
this DBAPI, making it incompatible with other PostgreSQL DBAPIs that all
work with plain bitstrings here.  A future fix in version 2.1 will
normalize this datatype across all PG backends.   Pull request courtesy
Sören Oldag.

Fixes: #10532
Closes: #10533
Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/10533
Pull-request-sha: 528607d5b5b67c1b9c2edb39176b7e005db371f6

Change-Id: I879f2fc24b5650467b85f170c0def15834f75456
---
 doc/build/changelog/unreleased_20/10532.rst   | 13 +++++++++++++
 lib/sqlalchemy/dialects/postgresql/asyncpg.py |  6 ++++++
 test/dialect/postgresql/test_types.py         |  9 ++++++++-
 3 files changed, 27 insertions(+), 1 deletion(-)
 create mode 100644 doc/build/changelog/unreleased_20/10532.rst

diff --git a/doc/build/changelog/unreleased_20/10532.rst b/doc/build/changelog/unreleased_20/10532.rst
new file mode 100644
index 0000000000..ba379cf768
--- /dev/null
+++ b/doc/build/changelog/unreleased_20/10532.rst
@@ -0,0 +1,13 @@
+.. change::
+    :tags: bug, postgresql
+    :tickets: 10532
+
+    Fixed SQL handling for "insertmanyvalues" when using the
+    :class:`.postgresql.BIT` datatype with the asyncpg backend.  The
+    :class:`.postgresql.BIT` on asyncpg apparently requires the use of an
+    asyncpg-specific `BitString` type which is currently exposed when using
+    this DBAPI, making it incompatible with other PostgreSQL DBAPIs that all
+    work with plain bitstrings here.  A future fix in version 2.1 will
+    normalize this datatype across all PG backends.   Pull request courtesy
+    Sören Oldag.
+
diff --git a/lib/sqlalchemy/dialects/postgresql/asyncpg.py b/lib/sqlalchemy/dialects/postgresql/asyncpg.py
index 4fa188cb02..ca35bf9607 100644
--- a/lib/sqlalchemy/dialects/postgresql/asyncpg.py
+++ b/lib/sqlalchemy/dialects/postgresql/asyncpg.py
@@ -205,6 +205,7 @@ from .base import PGExecutionContext
 from .base import PGIdentifierPreparer
 from .base import REGCLASS
 from .base import REGCONFIG
+from .types import BIT
 from .types import BYTEA
 from .types import CITEXT
 from ... import exc
@@ -237,6 +238,10 @@ class AsyncpgTime(sqltypes.Time):
     render_bind_cast = True
 
 
+class AsyncpgBit(BIT):
+    render_bind_cast = True
+
+
 class AsyncpgByteA(BYTEA):
     render_bind_cast = True
 
@@ -1016,6 +1021,7 @@ class PGDialect_asyncpg(PGDialect):
         {
             sqltypes.String: AsyncpgString,
             sqltypes.ARRAY: AsyncpgARRAY,
+            BIT: AsyncpgBit,
             CITEXT: CITEXT,
             REGCONFIG: AsyncpgREGCONFIG,
             sqltypes.Time: AsyncpgTime,
diff --git a/test/dialect/postgresql/test_types.py b/test/dialect/postgresql/test_types.py
index 95bbb16636..0a98ef5045 100644
--- a/test/dialect/postgresql/test_types.py
+++ b/test/dialect/postgresql/test_types.py
@@ -46,6 +46,7 @@ from sqlalchemy.dialects.postgresql import array
 from sqlalchemy.dialects.postgresql import array_agg
 from sqlalchemy.dialects.postgresql import asyncpg
 from sqlalchemy.dialects.postgresql import base
+from sqlalchemy.dialects.postgresql import BIT
 from sqlalchemy.dialects.postgresql import BYTEA
 from sqlalchemy.dialects.postgresql import CITEXT
 from sqlalchemy.dialects.postgresql import DATEMULTIRANGE
@@ -6233,7 +6234,8 @@ class PGInsertManyValuesTest(fixtures.TestBase):
     __backend__ = True
 
     @testing.combinations(
-        ("PG BYTEA", BYTEA(), b"7\xe7\x9f"),
+        ("BYTEA", BYTEA(), b"7\xe7\x9f"),
+        ("BIT", BIT(3), "011"),
         argnames="type_,value",
         id_="iaa",
     )
@@ -6266,6 +6268,11 @@ class PGInsertManyValuesTest(fixtures.TestBase):
 
         t.create(connection)
 
+        if type_._type_affinity is BIT and testing.against("+asyncpg"):
+            import asyncpg
+
+            value = asyncpg.BitString(value)
+
         result = connection.execute(
             t.insert().returning(
                 t.c.id,
-- 
2.47.2