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?
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' ] } ]
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With