postgreSQL error: "constraint does not exist" (but it does exist...)

Question

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'

Vinicius Braz Pinto · Accepted Answer · 2019-08-24 15:45:22Z

5

If traintimeday is not a constraint, instead of:

+'ON CONFLICT ON CONSTRAINT traintimeday '

use:

+'ON CONFLICT (traintimeday) '

answered Aug 24, 2019 at 15:45

Vinicius Braz Pinto

8,3094 gold badges45 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

garson Over a year ago

Hmm, then I get an error that "there is no unique or exclusion constraint matching the ON CONFLICT specification"

Vinicius Braz Pinto Over a year ago

@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: postgresqltutorial.com/postgresql-upsert

garson Over a year ago

that helped - thanks! i needed to define the constraint as a unique identifier.

Collectives™ on Stack Overflow

postgreSQL error: "constraint does not exist" (but it does exist...)

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related