2

I have a database that I'm trying to update, and I can't figure out why I'm getting this weird error about a column not existing. When I access the database using 'heroku pg:psql' I can totally see the column though. I have found a couple other questions with similar issues, but haven't been able to resolve it.

Here is my code to execute the INSERT...ON CONFLICT WITH CONSTRAINT...any ideas?

const text = 'INSERT INTO "test2" '
+'(route_id, secupdated, retrievedate, traintimeday) '
+'VALUES($1, $2, $3, $4) '
+'ON CONFLICT ON CONSTRAINT traintimeday '
+'DO UPDATE SET (secupdated, secarrival) = (excluded.secupdated, excluded.secarrival) '
+'RETURNING *' ; 
const values = [train_id 
, Math.round(dateNow.getTime()/1000)
, Math.round(dateNow.getDate())
, Math.round(dateNow.getDate()) + stu.stop_id
]; 
pool.query(text, values, (err, res) => {
if (err) {
throw err;
}
console.log('user:', res.rows[0])
})

The error message says that 'constraint traintimeday does not exist'

garson
  • 1,505
  • 3
  • 22
  • 56

1 Answers1

5

If traintimeday is not a constraint, instead of:

+'ON CONFLICT ON CONSTRAINT traintimeday '

use:

+'ON CONFLICT (traintimeday) '
Vinicius Braz Pinto
  • 8,209
  • 3
  • 42
  • 60
  • Hmm, then I get an error that "there is no unique or exclusion constraint matching the ON CONFLICT specification" – garson Aug 24 '19 at 21:41
  • 1
    @garson I don't know your data model, but assuming `route_id` is the primary key, you probably want to use `ON CONFLICT (route_id)`. This article explains how ON CONFLICT should be used: http://www.postgresqltutorial.com/postgresql-upsert/ – Vinicius Braz Pinto Aug 24 '19 at 21:56
  • that helped - thanks! i needed to define the constraint as a unique identifier. – garson Aug 24 '19 at 23:40