0

I am trying to use a CTE to insert some random data into a table -

create table foo (id integer)

with x as (select random())
  insert into foo (id)
  select x from x

This gives an error: ERROR: column "id" is of type integer but expression is of type record

Just the CTE with select works:

with x as (select random())
--   insert into foo (id)
  select x from x

I am also unable to typecast:

with x as (select random())
  insert into foo (id)
  select x::integer from x

This gives an error: ERROR: cannot cast type record to integer.

What's going wrong and how do I fix it?

1 Answer 1

5
select x from x

means select entire row as column (note the parentheses in output, not pure number). Like value of composite type.

For example,

melkij=> select pg_am.* from pg_am limit 1;
 oid | amname |      amhandler       | amtype 
-----+--------+----------------------+--------
   2 | heap   | heap_tableam_handler | t

melkij=> select pg_am from pg_am limit 1;
              pg_am              
---------------------------------
 (2,heap,heap_tableam_handler,t)

To get the value of a function, you need to access a specific column, not the entire row.

with x as (select random()) select x.random from x;
-- or
with x as (select random() as r) select r from x;
-- or
with x(r) as (select random()) select r from x;

you can use the value in insert

with x as (select random() as r)
  insert into foo (id)
  select r from x;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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