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

Create multi upsert query  #32

@Mkbewe

Description

@Mkbewe

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?

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions