Take the tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.

Im using postgres version 8.1. I want to use string to array and unnest functions. 8.1 does not support unnest so I've created it using code found via Google and stackoverflow.

My error message is as follows: ERROR: column "unnested" is of type text[] but expression is of type text HINT: You will need to rewrite or cast the expression.

There are multiple steps to the process which I'm including. However, when i set up my tables/columns I did designate them both as 'text[]' & I don't understand the part of the error message that says "expression is of the type text".

Create ‘temp’ table

Code:

CREATE TEMP TABLE temp
(words text[])
\g

CREATE TABLE

Create ‘end table’

Code:

CREATE TABLE endtable
(unnested text[])
\g

CREATE TABLE

Apply string to array function on ‘Oh My’ into ‘temp’ table

Code:

INSERT INTO temp(words)
SELECT STRING_TO_ARRAY('Oh My',' ')
\g

INSERT 0 1

Create unnest function

Code:

CREATE OR REPLACE FUNCTION UNNEST(anyarray) RETURNS SETOF ANYELEMENT
  LANGUAGE SQL AS
$$
SELECT $1[i] FROM GENERATE_SERIES(ARRAY_LOWER($1,1), ARRAY_UPPER($1,1)) AS i;
$$;

CREATE FUNCTION

Apply unnest function into 'endtable'

Error…Code:

INSERT INTO endtable(unnested)
SELECT unnest(words)
FROM temp
\g
ERROR:  column "unnested" is of type text[] but expression is of type text
HINT:  You will need to rewrite or cast the expression.

Thank you in advance for any help or explanation that you can provide.

share|improve this question
1  
BTW: pg-8.1 is very old. Have you thought about upgrading? –  wildplasser Dec 1 at 1:25
 
I agree it is very old however I do not have the choice of upgrading. Trust me if I could I would! –  nlstocks Dec 1 at 1:54
add comment

1 Answer

Your table column needs to be of type text , not the array type text[]:

CREATE TABLE endtable (unnested text);
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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