The following query works:
SELECT a, b FROM unnest(ARRAY[(1,2), (3,4)]) AS t(a integer, b integer); a b _ _ 1 2 3 2
However, I wasn't able to use a different column type such as
SELECT a, b FROM unnest(ARRAY[(1,'hello'), (3,'world')]) AS t(a integer, b varchar(255)); ERROR: 42804: function return row and query-specified return row do not match DETAIL: Returned type unkown at ordinal position 2, but query expects text.
It seems that, in the second case, the column type is inferred as
unknown, which is not cast to
How do I make the second example work and return columns with the right type, if possible without warnings and without modifying the
Background: I am trying to improve performance of large bulk insert operations using the
psycopg2 Python module, which does not support using multiple rows in
VALUES arguments. I stumbled onto the above example while trying out some other methods.