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.

Check the below answer.

dev1234
– dev1234

2013-12-06 16:21:45 +00:00
Commented Dec 6, 2013 at 16:21 — dev1234
– dev1234, Commented Dec 6, 2013 at 16:21

dev1234 · Accepted Answer · 2013-12-06 16:25:56Z

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

edited Dec 6, 2013 at 16:25

answered Dec 6, 2013 at 16:00

dev1234

5,75617 gold badges62 silver badges118 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user3042753 Over a year ago

Its not working. Also, I have tried with CDbExpression but it was displaying following error "CDbCommand failed to execute the SQL statement: SQLSTATE[42601]: Syntax error: 7 ERROR: syntax error at or near "$" " My Code: $books->name=new CDbExpression('array_append(name,$Name_key_array)');

dev1234 Over a year ago

what happens if you hard code the values like this to see if it works. array_append(columname, value). i.e array_append(name, 5)

user3042753 Over a year ago

If I use array directly then its works but not when using variable name.

dev1234 Over a year ago

Can you please update the question by stating the attempt you made now to resolve without variable name.

Collectives™ on Stack Overflow

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

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related