I have a SQL statement which works 100% fine and returns what I need.
select t1.*,sg.shape from species_geom sg join
(select sg.linkid,array_agg((st.nj_status,st.fed_status)) species
from species_geom sg join species_table st
on sg.linkid = st.linkid
group by sg.linkid)t1
on sg.linkid=t1.linkid
However when I try and create a table with the query it gives me this error
ERROR: column "species" has pseudo-type record[]
********** Error **********
ERROR: column "species" has pseudo-type record[]
SQL state: 42P16
can somebody give me a good explanation on why I cannot create this table and how to go about fixing this problem
ps* I am using create table species2 as
as my create table statement
And for anyone else who has been looking for an answer on the same error, as @ziggy mentioned casting array to text works.
below is the code snippet I have worked on to create a view when I encountered the same error
select * ,
case when foo > 0
then (case when bar > 0 then 'bar'end,
case when bar1 > 0 then 'bar1'end,
case when bar2 > 0 then 'bar2' end)
else NULL end :: varchar as result,
from table1;
this will provide the output as (bar, bar1, bar2) in one column.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With