feature request: add on conflict set to dynamic insert query helper

[alextes]

I have a query like so:

const insertAnalysisT = () => sql`
	  INSERT INTO base_fees_per_block (
		    hash,
		    number
	  )
	  VALUES (
		    ${block.hash},
		    ${block.number}
	  )
	  ON CONFLICT (number) DO UPDATE
	  SET
		    hash = ${block.hash},
		    number = ${block.number}
`;

Because of the conflict but I can't use the dynamic query helper. A helper that simply sets all columns that are inserted on a conflict with a specifiable list of columns (in this case just number but could be multiple), would be quite flexible and for me helpful. This query is a lot longer in actuality.

If there are others (👍 ) it may be interesting to implement!

[porsager]

Should by possible by doing this 🙂

const insertAnalysisT = () => sql`
  INSERT INTO base_fees_per_block 
    ${ sql(block) }
  ON CONFLICT (number) DO UPDATE
  SET ${ sql(block) }
`

[antonbrams]

unfortunatelly it doesn't work for me

const key = 'id'
const table = 'orders_status'
const rows = [
	{id: 2, title: 'test1'},
	{id: 3, title: 'test2'},
]
sql`
	INSERT INTO ${sql(table)} ${sql(rows)}
	ON CONFLICT (${key}) DO UPDATE
	SET ${sql(rows)}
`

even if i hard code the conflict id, it throws an error
"PostgresError: column "0" of relation "orders_status" does not exist"

what am i doing wrong?

[alextes]

I'm not sure but I think the issue is with the conflict clause:

ON CONFLICT (${key}) DO UPDATE

you're supposed to provide the name of the column, I believe for column names the syntax would be

`${sql(key)}`

Maybe. You can try ON CONFLICT (id) DO UPDATE to confirm that's what is going wrong. You can also use the debug option to see what SQL is actually being sent which might make the problem obvious. You can see a short description of it here: https://github.com/porsager/postgres#all-postgres-options

[antonbrams]

even if i hard code the conflict id, it throws an error

i've done it already :( and this was my error

"PostgresError: column "0" of relation "orders_status" does not exist"

[porsager]

You're missing sql() around key 😊

[antonbrams]

You're missing sql() around key 😊

i've tested it too... but please read carefully, what i've wrote - even this configuration

sql`
	INSERT INTO ${sql(table)} ${sql(rows)}
	ON CONFLICT (id) DO UPDATE
	SET ${sql(rows)}
`

throws an error "PostgresError: column "0" of relation "orders_status" does not exist"

[porsager]

Sorry. Replied on mobile, so missed the subsequent replies before writing. What version are you on?

…

On 15 Jun 2022, at 20.01, Anton Brams ***@***.***> wrote:  You're missing sql() around key 😊 i've tested it too... but please read carefully, what i've wrote - even this configuration sql` INSERT INTO ${sql(table)} ${sql(rows)} ON CONFLICT (id) DO UPDATE SET ${sql(rows)} ` throws an error "PostgresError: column "0" of relation "orders_status" does not exist" — Reply to this email directly, view it on GitHub, or unsubscribe. You are receiving this because you modified the open/close state.

[antonbrams]

no prob <3
"postgres": "^3.2.4"

[porsager]

Ah I see.. you can't supply an array (multiple rows) to update set like for inserts. You can only supply an object for a single row, so your query will work like that if you just have one row - eg.

sql`
  INSERT INTO ${sql(table)} ${ sql(row) }
  ON CONFLICT (${ sql(key) }) DO UPDATE
  SET ${ sql(row) }
`

If you want multiple rows to work and still be completely dynamic around columns you have to use fragments and then you can do it like this by using excluded.:

sql`
  INSERT INTO ${sql('test')} ${sql(rows)}
  ON CONFLICT (${ sql(key) }) DO UPDATE
  SET ${ 
    Object.keys(rows[0]).map((x, i) =>
      sql`${ i ? sql`,` : sql``}${ sql(x) } = excluded.${ sql(x) }`
    )
  }
`

[antonbrams]

If you want multiple rows to work and still be completely dynamic around columns you have to use fragments and then you can do it like this by using excluded.:

sql`
  INSERT INTO ${sql('test')} ${sql(rows)}
  ON CONFLICT (${ sql(key) }) DO UPDATE
  SET ${ 
    Object.keys(rows[0]).map((x, i) =>
      sql`${ i ? sql`,` : sql``}${ sql(x) } = excluded.${ sql(x) }`
    )
  }
`

jesus..... this is such a crazy syntax :D
thanks, i'll try tomorrow!

[porsager]

Crazy indeed, but it's whats keeping you safe from sql injections 😝

For your sample above it generates the following query:

INSERT INTO "test" ("id","title")values($1,$2),($3,$4)
ON CONFLICT ("id") DO UPDATE
SET  "id" = excluded."id", "title" = excluded."title"

[antonbrams]

thanks! It works!

[karlhorky]

Nice, this syntax for excluded. keys is great for INSERT INTO ... ON CONFLICT queries (upserts)

Here's another version with more expressive variable names and TypeScript:

export async function upsertBenefitsUnauthenticated(benefits: BenefitInput[]) {
  return await sql<Benefit[]>`
    INSERT INTO benefits
      ${sql(benefits)}
    ON CONFLICT
      (id)
    DO UPDATE SET
      ${Object.keys(benefits[0]!).map((field, count) =>
        sql`${count > 0 ? sql`,` : sql``} ${sql(field)} = excluded.${sql(field)}`,
      )}
    RETURNING
      *
  `;
}

[karlhorky]

Oh interesting, this query above is giving me a TypeScript error though, hm... 🤔 Wonder if the types for ParameterOrFragment or sql() are missing something cc @Minigugus

Argument of type 'PendingQuery<Row[]>[]' is not assignable to parameter of type 'ParameterOrFragment<never>'.
  Type 'PendingQuery<Row[]>[]' is not assignable to type 'readonly SerializableParameter<never>[]'.
    Type 'PendingQuery<Row[]>' is not assignable to type 'SerializableParameter<never>'.
      Type 'PendingQuery<Row[]>' is missing the following properties from type 'Uint8Array': BYTES_PER_ELEMENT, buffer, byteLength, byteOffset, and 26 more.ts(2345)

[karlhorky]

Apparently it's a problem with the ParameterOrFragment type, opened PR #613 to fix