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 varchar(255)
:
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 varchar(255)
automatically.
How do I make the second example work and return columns with the right type, if possible without warnings and without modifying the ARRAY[...]
definition?
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.
VALUES
. The following works just fine for me:cur.execute('INSERT INTO foo VALUES (%s, %s), (%s, %s), (%s, %s)', (1, 'foo', 2, 'bar', 3, 'baz'))
cur.execute('INSERT INTO too VALUES %s', (list_of_rows,))
doesn't exist.