Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

postgresql cannot create table with pseudo-type record[]

Tags:

sql

postgresql

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

like image 939
ziggy Avatar asked Jun 08 '17 18:06

ziggy


1 Answers

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.

like image 58
Ravi Naidu Avatar answered Nov 05 '22 11:11

Ravi Naidu