HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

Unknown returned type in PostgreSQL query

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
postgresqlreturnedunknownquerytype

Problem

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.

Solution

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)

Code Snippets

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

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

Context

StackExchange Database Administrators Q#100665, answer score: 8

Revisions (0)

No revisions yet.