I'm trying to update a lot of row in my database using unnest(). Here's my db: Then I update the table with: But I got the following error: However, it's working if I do a classic update. Any idea ? Answer Since your data is a 2x100 matrix, is better to partition it in vectors (1d array) for each parameter

psycopg update with unnest for a bytea column

I’m trying to update a lot of row in my database using unnest(). Here’s my db:

create table my_table
(
    id         serial,
    feat       bytea,
    primary key (id)
);

create index ix_my_table_id
    on my_table (id);

Then I update the table with:

import numpy as np

data = [(np.random.random(20).tobytes(), i) for i in range(100)]
cursor.execute(
    """
    UPDATE my_table
    SET feat = s.feat
    FROM unnest(%s) s(feat bytea, id integer)
    WHERE id = s.id;
    """, (data,),
)

But I got the following error:

  File ".../python3.8/site-packages/psycopg/cursor.py", line 551, in execute
    self._conn.wait(
  File ".../python3.8/site-packages/psycopg/connection.py", line 776, in wait
    return waiting.wait(gen, self.pgconn.socket, timeout=timeout)
  File ".../python3.8/site-packages/psycopg/waiting.py", line 219, in wait_epoll
    s = next(gen)
  File ".../python3.8/site-packages/psycopg/cursor.py", line 196, in _execute_gen
    pgq = self._convert_query(query, params)
  File ".../python3.8/site-packages/psycopg/cursor.py", line 379, in _convert_query
    pgq.convert(query, params)
  File ".../python3.8/site-packages/psycopg/_queries.py", line 84, in convert
    self.dump(vars)
  File ".../python3.8/site-packages/psycopg/_queries.py", line 95, in dump
    self.params = self._tx.dump_sequence(params, self._want_formats)
  File ".../python3.8/site-packages/psycopg/_transform.py", line 152, in dump_sequence
    out[i] = dumper.dump(param)
  File ".../python3.8/site-packages/psycopg/types/array.py", line 167, in dump
    dump_list(obj)
  File ".../python3.8/site-packages/psycopg/types/array.py", line 154, in dump_list
    ad = self._dump_item(item)
  File ".../python3.8/site-packages/psycopg/types/array.py", line 173, in _dump_item
    return self.sub_dumper.dump(item)
  File ".../python3.8/site-packages/psycopg/types/composite.py", line 66, in dump
    return self._dump_sequence(obj, b"(", b")", b",")
  File ".../python3.8/site-packages/psycopg/types/composite.py", line 47, in _dump_sequence
    ad = b'"' + self._re_esc.sub(rb"11", ad) + b'"'
  File ".../3.8.10/lib/python3.8/re.py", line 332, in filter
    return sre_parse.expand_template(template, match)
  File ".../3.8.10/lib/python3.8/sre_parse.py", line 1064, in expand_template
    return empty.join(literals)
AttributeError: 'memoryview' object has no attribute 'join'

However, it’s working if I do a classic update. Any idea ?

Python: 3.8.10
psycopg: 3.0.13

Answer

Since your data is a 2×100 matrix, is better to partition it in vectors (1d array) for each parameter as follows:

cursor.execute(
        """
        UPDATE my_table mt
        SET feat = s.feat
        FROM (select unnest(%s) id, unnest(%s) feat) s 
        WHERE mt.id = s.id;
    """, ([row[1] for row in data],[row[0] for row in data]),)

Advertisement

Answer