How to insert ARRAY data type using knex and potsgres

Question

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 are referring data.labelG and data.resultG, but you are initializing data.label and data.result — Mikael Lepistö
– Mikael Lepistö, Commented May 1, 2018 at 19:43

Mikael Lepistö · Accepted Answer · 2018-05-01 19:41:45Z

29

You can directly pass javascript arrays to your ARRAYtype 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' ] } ]

answered May 1, 2018 at 19:41

Mikael Lepistö

19.8k4 gold badges73 silver badges74 bronze badges

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

Comments

hurricane · Accepted Answer · 2018-05-01 14:31:59Z

1

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.

answered May 1, 2018 at 14:31

hurricane

6,7342 gold badges38 silver badges45 bronze badges

1 Comment

Mikael Lepistö Over a year ago

String 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.

Collectives™ on Stack Overflow

How to insert ARRAY data type using knex and potsgres

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related