8

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.

  • I'm not sure why you state that psycopg2 doesn't support multiple rows in 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')) – Dave Jones May 6 '15 at 20:47
  • What I mean is that it doesn't support an arbitrary number of rows, e.g. something like cur.execute('INSERT INTO too VALUES %s', (list_of_rows,)) doesn't exist. – F.X. May 6 '15 at 21:16
  • Ahh, and you're hoping to substitute the array as a single parameter, I see. – Dave Jones May 6 '15 at 21:26
6

You can do this without generating a warning by creating a type and casting the records to it:

create type t as (a integer, b varchar(255));

select * from unnest(array[(1,'hello'), (3,'world')]::t[]);
┌───┬───────┐
│ a │   b   │
├───┼───────┤
│ 1 │ hello │
│ 3 │ world │
└───┴───────┘

tested on 9.4 and 9.3 (db<>fiddle here)

7

It is ugly, but you can try:

SELECT a, b::text
FROM unnest(ARRAY[(1,'hello'), (3,'world')])
AS t(a integer, b unknown);

This way the type defined in AS matches the output of unnest(), which you can cast to your needs in the SELECT list.

You can try this in a small SQLFiddle.

1

Should do it:

SELECT a, b
FROM unnest(ARRAY[(1,varchar 'hello'), (3,varchar 'world')])
AS t(a integer, b varchar(255));
  • 1
    This works, however I wasn't able to force psycopg2 to include type casts inside the ARRAY[...] definition. Is it possible to do it without? I edited my question to reflect that. – F.X. May 5 '15 at 14:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.