INSERT ON CONFLICT syntax

[esatterwhite]

I'm trying to convert a query that uses a common table express with an insert + on conflict clause, I'm not having much luck

WITH vanity AS (
  INSERT INTO vanity_ids (organization_id, counter) 
  VALUES ($1, $2)
  ON CONFLICT (organization_id) DO UPDATE
   SET counter = vanity_ids.counter + 1
)
...

const pg = require('@sqorn/pg')
const vanity =
  pg
    .from `vanity_ids (organization_id, counter)`
    .insert`values (${1}, ${1})`
    .sql`ON CONFLICT (organization_id) ON CONFLICT`
    .sql`SET counter = vanity_ids.counter + 1`
    .returning`counter`

pg.with({vanity})

[lalitkapoor]

Is it possible to combine calls to .sql with non-manual query generation methods (in this case insert)?

Btw, I think this might work for your use-case:

const sqorn = require('@sqorn/pg')
const sq = sqorn({})

const organization_id = 1
const counter = 1

const vanity =
  sq
    .from`vanity_ids`
    .insert`
      values (${organization_id}, ${counter})
      ON CONFLICT (organization_id) DO UPDATE
      SET COUNTER = vanity_ids.counter +  1
    `
    .return`counter`

console.log(sq.with({vanity}).query)

[lalitkapoor]

Alternatively

const  values =
  sq
    .txt`values (${organization_id}, ${counter})`
    .txt`ON CONFLICT (organization_id) DO UPDATE`
    .txt`SET COUNTER = vanity_ids.counter +  1`

const vanity =
  sq
    .from`vanity_ids`
    .insert`${values}`
    .return`counter`

[hlobil]

How would one add 'ON CONFLICT ...' to bulk insert operation?

[lalitkapoor]

@hlobil I think you can just do the following to generate the values for a bulk insert. From the docs: https://sqorn.org/docs/manual-queries.html#link

Your ON CONFLICT clause stays the same