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?