bug: Postgres cannot yet reliably handle null typed columns

[lmores]

What happened?

I am trying to insert into this PostgreSQL table

CREATE TABLE address (
    id bytea NOT NULL,
    another_id bytea NULL,
    full_address varchar NULL,
    recipient varchar NULL,
    street varchar NULL,
    street2 varchar NULL,
    "number" varchar NULL,
    zip_code varchar NULL,
    city varchar NULL,
    subdivision_code varchar NULL,
    country_code varchar NULL,
    CONSTRAINT raw_address_pkey PRIMARY KEY (id)
);

the following instance of a Python dataclass

@dataclass(frozen=True, slots=True)
class Address:
    id: bytes
    another_id: bytes | None
    full_address: str

    recipient: str | None = None
    street: str | None = None
    street2: str | None = None
    number: str | None = None
    zip_code: str | None = None
    city: str | None = None
    subdivision_code: str | None = None
    country_code: str | None = None

record = Address(
    id=b'...some bytes...',
    another_id=None,
    full_address='SOME TEXT',
    recipient='FOO',
    street='BAR STREET',
    street2=None, number='',
    zip_code='ZIP',
    city='AA',
    subdivision_code='US',
    country_code=None
)

con  # PostgresBackend instance
con.insert('address', [record])

However, I get the following error:

Traceback (most recent call last):
  File "**OMISSIS**/client.py", line 53, in insert_addresses
    self.con.insert('address', [record])  # type: ignore
    ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "**OMISSIS**/.venv/lib/python3.12/site-packages/ibis/backends/sql/__init__.py", line 405, in insert
    self._run_pre_execute_hooks(obj)
  File "**OMISSIS**/.venv/lib/python3.12/site-packages/ibis/backends/__init__.py", line 1144, in _run_pre_execute_hooks
    self._register_in_memory_tables(expr)
  File "**OMISSIS**/.venv/lib/python3.12/site-packages/ibis/backends/__init__.py", line 1119, in _register_in_memory_tables
    self._register_in_memory_table(memtable)
  File "**OMISSIS**/.venv/lib/python3.12/site-packages/ibis/backends/postgres/__init__.py", line 112, in _register_in_memory_table
    raise exc.IbisTypeError(
ibis.common.exceptions.IbisTypeError: postgres cannot yet reliably handle `null` typed columns; got null typed columns: ['another_id', 'street2', 'country_code']

I am not sure about its meaning, seems that Ibis does not support the insertion of NULL values even in columns of Postgres tables that allow NULL values... is it?

What version of ibis are you using?

9.5.0

What backend(s) are you using, if any?

PostgreSQL 13.5

Relevant log output

No response

Code of Conduct

• I agree to follow this project's Code of Conduct

[hottwaj]

Hey the exception is due to something slightly different:

1. ibis is creating a temporary table that contains the data you want to insert
2. Ibis guesses the column type for each column in the temporary table using the data you provide (it does not look at the type annotations in your dataclass)
3. Some of the data you provided is None. Ibis infers that it needs to use a column of type Null for those each of those fields
4. Ibis throws an exception because it cannot handle columns of type Null in the postgres backend

I'm not sure if ibis is designed to support inserting using dataclass instances (your approach above). Someone else can confirm

Assuming it's not something that ibis supports, you'd need to build something yourself that sets the column types correctly on the temporary table as ibis won't use your type annotations

[lmores]

Hi, thank you for the useful info. I am a newbie with Ibis and I am struggling to find out a way to properly define the type of each field/column before passing the record(s) to the PostgresBackend. Could you point me to the relevant part of the docs? (or provide an example for this case?)

More in general, are these implementation details of Ibis documented somewhere? At the moment, I am pretty good at manipulating the content of a table, but when it comes to insert data from an external source or extract the records to dump them somewhere else outside the realm of Ibis I am pretty limited.

Side note: the code above works fine when using a DuckDbBackend and a dataclass instance.

[lmores]

Found it out:

con.insert('address', ibis.memtable([record], schema=my_table_schema))