Skip to content
This repository has been archived by the owner on Mar 25, 2022. It is now read-only.

How to handle arrays as records for INSERTS and UPDATES #22

Open
joshtwist opened this issue Sep 19, 2020 · 5 comments
Open

How to handle arrays as records for INSERTS and UPDATES #22

joshtwist opened this issue Sep 19, 2020 · 5 comments

Comments

@joshtwist
Copy link

How would I best use pg-format to handle the insertion of arrays into columns with an array type.

Imagine a table with a column 'roles' of type text[].

To insert into that table the statement would look something like

INSERT INTO table (name, roles) VALUES ("Josh", ARRAY["role1", "role2"])
or
INSERT INTO table(name, roles) VALUES ("Josh", {"role1", "role2"})

However, when pg-format encounters an array it formats as a literal like this:

INSERT INTO table(name, roles) VALUES ("Josh", ("role1", "role2")) which is invalid. That format ("role1", "role2") works great for IN statements so clearly is the correct handling for arrays in some circumstances.

Recommendations for UPDATES and INSERTS where the value being written is an Array?

@rpedela
Copy link

rpedela commented Sep 19, 2020

Arrays are expanded without any surrounding parentheses so that you can use IN(%L) or ARRAY[%L].

const format = require('pg-format');
var sql = format('INSERT INTO my_table (a, b) VALUES (%L, ARRAY[%L])', 'abc', [ 1, 2, 3 ]);
console.log(sql); // INSERT INTO my_table (a, b) VALUES ('abc', ARRAY['1','2','3'])'

@joshtwist
Copy link
Author

joshtwist commented Sep 19, 2020

I'm currently using this form

const format = require('pg-format');
const sql = format("INSERT INTO my_table %I VALUES (%L)", ['array', 'of', 'keys'], ['array', 'values', [1,2,3]]);

It's a dynamic data situation so I don't know the number of columns before runtime. I suspect the best solution is to dynamically generate a format string like this based on the number of properties and if a value has an array datatype?

INSERT INTO my_table %I, %I, %I VALUES (%L, %L, ARR[%L])

Thanks for prompt reply.

@KarmitoHerry
Copy link

I have same characteristic problem if we doing batch insert.

My objective on my query :

INSERT INTO vitamin(name,composition ) VALUES ('Multi Vitamin', [{"option":"vitamin A", "miligram":8mg},{"option":"vitamin B", "miligram":8mg}, {"option":"vitamin D", "miligram":10mg}]), ('Fibre Blend', [{"option":"Protein", "miligram":8mg},{"option":"Fat", "miligram":8mg}, {"option":"insoluble fibre", "miligram":10mg}]);

The solution from comment above cannot applied on batch insert like my objective query.

If I do looping the code , like this

const format = require('pg-format');

var dataVitamin = []

for(let data of vitaminList)
{
       var name = data.name;
       var nutritions = data.nutritions
       var params = [name, nutritions];
       dataVitamin.push(params);
}

const query = format('INSERT INTO vitamin(name, composition) VALUES %L', dataVitamin);
console.log(query);

The console.log result :

INSERT INTO vitamin(name,composition ) VALUES 
('Multi Vitamin', {"option":"vitamin A", "miligram":8mg}::jsonb,{"option":"vitamin B", "miligram":8mg}::jsonb, {"option":"vitamin D", "miligram":10mg}::jsonb), 
('Fibre Blend', {"option":"Protein", "miligram":8mg}::jsonb,{"option":"Fat", "miligram":8mg}::jsonb, {"option":"insoluble fibre", "miligram":10mg}::jsonb)

when we run the query on my code, error come up : ERROR : VALUES list must have same length

if i convert my code above become like this

const format = require('pg-format');

// this type of code, get my objective
for(let data of vitaminList)
{
       var name = data.name;
       var nutritions = data.nutritions
       const query = format('INSERT INTO vitamin(name, composition) VALUES (%L, ARRAY[%L])', name, nutritions);
       console.log(query);
}

the converted code serve the purpose of the objective , but if we do loop for 10.000 rows data on single run.
sometimes error just show up too many request on connections

Any solution for this issue ?

Thank you

@meetpaija
Copy link

@KarmitoHerry , Facing the same, can anyone help?
Thank you!

@rpedela
Copy link

rpedela commented Aug 5, 2021

In cases where format() cannot do what you need out of the box, I recommend composing pieces into the final query using %s. And you can use the built-in JS type checking functions/syntax if you need to dynamically handle an array vs non-array.

let piece1 = format('%L', [1, 2, 3]);
let piece2 = format('%L, %L, ARRAY[%s]', 'a', 'b', piece1);
let query = format('insert into a, b, c values (%s)', piece2);

Sign up for free to subscribe to this conversation on GitHub. Already have an account? Sign in.
Labels
None yet
Projects
None yet
Development

No branches or pull requests

4 participants