PostgreSQL Composite Types INSERT INTO SELECT Needs Casting-CodePudding

I want to INSERT some records from a query into a table that has a column with the type of a composite type.

create type ratio as (numerator bigint, denominator bigint);

create table my_ratios(id bigint, val ratio);

Inserting some values naturally works:

insert into my_ratios (id, val) values (1, (22, 7)) returning *;

But this does not:

-- this does not work:
insert into my_ratios 
SELECT 
   round(n::float / d * 100)
 , (n, d) as ratio -- cannot cast type record to ratio
FROM generate_series(21, 23) n
   , generate_series(6, 8) d
returning *;

I found that if I cast the rowtype to text and cast the text back to my composite type, then the query works. But this is quite ugly:

insert into my_ratios 
SELECT 
  round(n::float / d * 100)
, cast(cast((n, d) as text) as ratio) -- note two casts
FROM generate_series(21, 23) n
   , generate_series(6, 8) d
returning *;

Is there a special syntax that I can use here?

SQL Fiddle

CodePudding user response：

You need to cast the tuple in the SELECT list:

SELECT round(n::float / d * 100), 
       (n, d)::ratio 
FROM ...

alternatively you can use cast( (n, d) as ratio)

Note that (a,b) as ratio just assigns an alias for the column. It has no implication on the actual data type of the column