PostgreSQL XML data type not supported

[nj1973]

Test case from a customer validating Oracle vs PostgreSQL.

PostgreSQL:

CREATE TABLE dvt_test.tab_xml
( id numeric(1000,0) NOT NULL PRIMARY KEY
, batch_id numeric(1000,0) NOT NULL
, xml_data xml);

Oracle:

CREATE TABLE dvt_test.tab_xml
( id NUMBER(*,0) NOT NULL PRIMARY KEY
, batch_id NUMBER(*,0) NOT NULL
, xml_data NCLOB);

Schema validation:

$ data-validation -v validate schema --source-conn ora --target-conn pg --tables-list "dvt_test.tab_xml"
...
    cursor.execute(statement, parameters)
psycopg2.errors.SyntaxError: syntax error at or near "dvt_test"
LINE 1: ...EMPORARY VIEW d8c496e55b324f97acba672aa8610c88 AS dvt_test.t...

Column validation:

$ data-validation -v validate column --source-conn ora --target-conn pg --tables-list "dvt_test.tab_xml"
...
    cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (psycopg2.errors.SyntaxError) syntax error at or near "dvt_test"
LINE 1: ...PORARY VIEW "26d25695f1eb44ea9fbec0b11ac6190c" AS dvt_test.t...
                                                             ^
[SQL: CREATE TEMPORARY VIEW "26d25695f1eb44ea9fbec0b11ac6190c" AS dvt_test.tab_xml]
(Background on this error at: https://sqlalche.me/e/14/f405)

Row validation:

$ data-validation -v validate row --source-conn ora --target-conn pg --tables-list "dvt_test.tab_xml" -pk=id --hash="*"
...
    cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (psycopg2.errors.SyntaxError) syntax error at or near "dvt_test"
LINE 1: ...PORARY VIEW "52ee1651e62a4392965f1569fe1a5792" AS dvt_test.t...
                                                             ^
[SQL: CREATE TEMPORARY VIEW "52ee1651e62a4392965f1569fe1a5792" AS dvt_test.tab_xml]
(Background on this error at: https://sqlalche.me/e/14/f405)

[nj1973]

It looks like we can prevent the exception with changes like below, these result in a datatype of unknown which matches what Ibis has done in more recent releases (ibis-project/ibis#8610):

--- a/third_party/ibis/ibis_postgres/datatypes.py
+++ b/third_party/ibis/ibis_postgres/datatypes.py
@@ -13,10 +13,23 @@
 # limitations under the License.

 import ibis.expr.datatypes as dt
+from sqlalchemy.sql import sqltypes
 from sqlalchemy.dialects import postgresql
-from sqlalchemy.dialects.postgresql.base import PGDialect
+from sqlalchemy.dialects.postgresql.base import PGDialect, ischema_names
+
+
+class XML(sqltypes.TypeEngine):
+    __visit_name__ = "XML"
+
+
+ischema_names["xml"] = XML


 @dt.dtype.register(PGDialect, postgresql.OID)
 def sa_pg_oid(_, sa_type, nullable=True):
     return dt.int32(nullable=nullable)
+
+
[email protected](PGDialect, XML)
+def sa_postgres_xml(_, satype, nullable=True):
+    return dt.Unknown(nullable=nullable)

Obviously this only prevents the CREATE VIEW exception. We still won't have XML support.