How Can Use Postgresql Array_Append() And Array_Remove() In Yii?

Question

How can use PostgreSQL array_append() and array_remove() in Yii? I am trying to update array type attribute on following table rows-

CREATE  TABLE Books(
id INT NOT NULL PRIMARY KEY,  
name UUID[])

Sample data rows: 
1       NULL
2       NULL

$books=Books::model()->findByPk($id);
$books->name='array_append(name, $Name_key_array)';
$books->save();

I have found following error:

CDbException
CDbCommand failed to execute the SQL statement: SQLSTATE[22P02]: Invalid text representation: 7 ERROR: array value must start with "{" or dimension information

But If I use array directly then its work but not when using variable name i.e.

array_append({1,2,3}, {4})

Also, I already tried few more ways but haven't found any success. I hope will find some great ideas to fix this issue.

Answer 1

I think you are using the array_append() in other way. it should be like this

SELECT array_append(ARRAY[1,2], 3);
 array_append
--------------
 {1,2,3}
(1 row)

So if i correct yours, it should be

$books->name='array_append($Name_key_array, name)';

for removing elements from an array, check this solution: http://www.youlikeprogramming.com/2013/06/removing-values-from-a-postgresql-array/

to an array in PostgreSQL data must be inserted in the following way,

INSERT INTO table (arr_col) VALUES ('{val1,val2, val3}');

http://www.postgresql.org/docs/9.1/static/arrays.html