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

Create multi upsert query #32

Open
Mkbewe opened this issue Nov 18, 2021 · 0 comments
Open

Create multi upsert query #32

Mkbewe opened this issue Nov 18, 2021 · 0 comments

Comments

@Mkbewe
Copy link

Mkbewe commented Nov 18, 2021

i want to create method to do upsert using library pg-format.

Example, the id is serial field.

// Example data to save:
const data = [
    {id: null, name:'a', desc: 'b'},
    {id: 20, name: 'aa', desc: 'bb'}
]

const sql = 'INSERT INTO table_name(id, a, b)
    VALUES(%L)
    ON CONFLICT (id)
    DO UPDATE SET (a,b) = 
    (EXCLUDED.a, EXCLUDED.b)';

const params = []
for (obj of data) {
    const id = obj.id ?? 'DEFAULT';
    params.push([id, obj.name, obj.desc])
}

format(sql, params); 
// Result: 
//  INSERT INTO table_name(id, a, b)
//  VALUES('DEFAULT', 'a', b), ('20', 'aa', 'bb')
//  ON CONFLICT (id)
//  DO UPDATE SET (a,b) = 
//  (EXCLUDED.a, EXCLUDED.b)

So I got an error because "DEFAULT" is in quotation marks, so it is a string and not the keyword postgres. I try using %I but it also not work. The only way i found was to use %s but it is not esceped so it's unsave.
I also try to make combination of this marks like this VALUES(%s, %L) or VALUES(%I, %L) but i i can have both id as a number and as a null so it's a incorrect way.

Mayby is diffret way to create upsert query or to bind this?

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

1 participant