patternsqlMinor
Unknown returned type in PostgreSQL query
Viewed 0 times
postgresqlreturnedunknownquerytype
Problem
The following query works:
However, I wasn't able to use a different column type such as
It seems that, in the second case, the column type is inferred as
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
SELECT a, b
FROM unnest(ARRAY[(1,2), (3,4)])
AS t(a integer, b integer);
a b
_ _
1 2
3 2However, 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:
tested on 9.4 and 9.3 (db<>fiddle here)
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.