How to insert ARRAY data type using knex and potsgres
11,716
Solution 1
You can directly pass javascript arrays to your ARRAY
type of columns. Like this:
await knex.schema.createTable('foo', t => {
t.increments('id');
t.specificType('intarray', 'integer ARRAY');
t.specificType('stringarray', 'text ARRAY');
});
await knex('foo').insert({ intarray: [4,3,2,1], stringarray: ['foo','bar'] });
const rows = await knex('foo');
console.log(rows);
// should output:
// [ anonymous { id: 1, intarray: [ 4,3,2,1 ], stringarray: [ 'foo', 'bar' ] } ]
Solution 2
For Postgresql's text array you need to store your data like:
{'Strengths','Weaknesses','Opportunities','Threats'}
For doing that you can create a function to convert it to common use.
db('analyses').insert({
userid: data.id,
choice: data.choice,
Label: '{"' + data.labelG.join('","') + '"}',
Results: data.resultG,
description: data.description
})
Also you need to convert it when you fetched them too.
Author by
SK12345
Updated on June 07, 2022Comments
-
SK12345 almost 2 years
I'm encountring this issue and i'm running out of time so please if anyone could help: I want to insert this data:
const data= { id:user.id, choice:'SWOT', label:['Strengths','Weaknesses','Opportunities','Threats'], results:[45,5,20,30], description:'My first Strategic Analysis'}
into this table:
analyses ( id serial primary key, userID integer not null, choice varchar(25) not null, Label text ARRAY, Results integer ARRAY, description varchar(200), FOREIGN KEY (userID) REFERENCES users (id)
);
Using knex, this should be smth like:
db('analyses').insert({ userid: data.id, choice: data.choice, Label: data.labelG, Results: data.resultG, description: data.description })
Since this syntax doesn't work for ARRAY types I wonder how to do it ? Some suggested to use knex.raw() however I coudn't get the right syntax for that Any help?
-
Mikael Lepistö about 6 yearsString generated in this answer seems to be like
{"str1","str2"}
instead of{'str1','str2'}
. However I suppose pg driver accepts that style too, since this worked for the person asking the question.