CODEWITHSUNDEEP
arrayspostgresqltypespostgresql-8.1
nlstocks
nlstocks

Reputation: 1

Postgres 8.1 error message (data type)

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.

Upvotes: 0

Views: 212

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656321

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

CREATE TABLE endtable (unnested text);

Upvotes: 2

Related Questions