PostgreSQL Composite Types INSERT INTO SELECT Needs Casting

Question

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

user330315 · Accepted Answer · 2022-07-18 13:34:24Z

1

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

answered Jul 18, 2022 at 13:34

user330315

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL Composite Types INSERT INTO SELECT Needs Casting

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related