PostgreSQL insert from SQL SELECT statement fails because of expression type text

Question

I'm running into a simple error on PostgreSQL inserting data into a new table. I'd like to use a simple query because this table is only going to store averages across different dimensions. I want my avg column to be double precision. My insert statement is

insert into benchmark_table 
(select avg(s.percentage_value) as avg, s.metric_name, s.category 
from some_table s group by s.category, s.metric_name);

This command fails with the following error:

ERROR: column "avg" is of type double precision but expression is of type text LINE 2: ...(s.percentage_value) as double precision) as avg, s.metric_n... ^ HINT: You will need to rewrite or cast the expression.

So I try casting my avg column to double precision:

INSERT into benchmark_table 
(SELECT cast(avg(s.percentage_value) as double precision) as avg, s.metric_name, s.category 
FROM some_table s group by s.category, s.metric_name);

I've also attempted

insert into benchmark_table 
(Select  avg(s.percentage_value)::double precision as avg, s.metric_name, s.category 
from summary_view_output s group by s.category, s.metric_name);

However, I get the same error about avg being text. I understand that what's being returned from my query is a result set that is by default text, but I'm not seeing any way to convert this into another datatype for my outer INSERT statement to use.

try changing the ::double precision to ::float? also, maybe the ordinal position of the tables fields are not the same as your select. try to use "insert into benchmark_table( avg, metric_name, category)" — Ed Mendez
– Ed Mendez, Commented Jul 3, 2018 at 17:28
yes, this worked. feel free to write this up as an answer and I'll accept — Yu Chen
– Yu Chen, Commented Jul 3, 2018 at 17:49

Ed Mendez · Accepted Answer · 2018-07-03 18:20:49Z

3

Try to change the ::double precision to ::float and see if that works. Also I noticed you aren't including the field names in the Insert clause. Maybe the ordinal position of the fields of the benchmark_table is not the same as in the select statement. try to use.

insert into benchmark_table( avg, metric_name, category)

answered Jul 3, 2018 at 18:20

Ed Mendez

1,77312 silver badges17 bronze badges

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

1 Comment

Ben Wilson Over a year ago

Ordinal position FTW

Collectives™ on Stack Overflow

PostgreSQL insert from SQL SELECT statement fails because of expression type text

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related