I have a table in my Postgres database which contains a column with datatype ARRAY. I am using Bookshelf to perform operations on the database. Now, I want to insert/update (append new data to previous data in an array) data in this column and I could not find a way to do this. Can anybody guide me on how I can achieve this?
I think, one way of doing this could be using raw() function of Knex but I am not sure about how to use raw() function so please guide me on that too.
Thanks.
Add a comment
|
3 Answers
I found the solution for this problem here. It seems BookshelfJS does not have a way to handle such operations so I had to use KnexJS. I implemented it something like this -
knex('users') //users table
.where('id', id)
.update({
array_column_name: knex.raw('array_append(array_column_name, ?)', [data_to_append])
})
.then(function (user) {
//Do something
});
Hope this helps other people in future.
Comments
Assuming the following table schema
CREATE TABLE test (id SERIAL PRIMARY KEY, data TEXT[] NOT NULL);
with example data
INSERT INTO test (data) VALUES (array[ 'def', 'ghi' ]);
which can be queried like
SELECT * FROM test ;
id | data
----+-----------
1 | {def,ghi}
You can use the array functions to array_prepend( 'abc', array ) and array_append( array, 'xyz' ) like so
UPDATE test SET data = array_prepend( 'abc', data );
UPDATE test SET data = array_append( data, 'xyz' );
to get
SELECT * FROM test;
id | data
----+-------------------
1 | {abc,def,ghi,xyz}
You should be aware that the data column is not atomic and therefore this table schema does not adhere to first normal form (violation of 1NF). It will be more difficult to filter out values from the data column. You cannot use the WHERE clause easily, for example. Consider adapting the table schema to adhere to 1NF, better 3NF at least.
1 Comment
zerosand1s
thanks for your reply. I am aware of these
array functions but I want to achieve this using BookshelfJS and KnexJS. Could you please guide me on that?UPDATE tableName
SET ColumnName = array_prepend( 'Value', ColumnName)
WHERE ColumnName = 'Value'
UPDATE tblTest
SET TestColumnName = array_prepend( 'TestData', TestColumnName)
WHERE TestColumnId = '1'
