0

I don't know much of a pSQL, however the behaviour that i want is to:

insert a key (string type) - value(string type) into a public table (named stats)

Code i am using is like this

let value = await db.query({
                text: "INSERT INTO public.stats (key,value) values ($1,$2) ON CONFLICT (key) DO UPDATE SET value = $2;",
                values: [wantedStamp,inDb] 
            });
            const rep: Reply_ip_get_background_image_timestamp = {
                name: wantedStamp,
                date: inDb
            };
            res.json(rep).status(200);

Where wantedStamp is a string variable declared before, inDb is the string form of date. When i try to use this i get the error:

error: there is no unique or exclusion constraint matching the ON CONFLICT specification
...some modeule errors amd than...
length: 148,
  severity: 'ERROR',
  code: '42P10',
  detail: undefined,
  hint: undefined,
  position: undefined,
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'plancat.c',
  line: '842',
  routine: 'infer_arbiter_indexes'

EDIT------ Now my code is like

let value = await db.query({
                text: "INSERT INTO stats (key,value) VALUES($1,$2) ON CONFLICT (key) WHERE ((key)::text = $1::text) DO UPDATE SET value = $2;",
                values: [wantedStamp,inDb] 
            });
            const rep: Reply_ip_get_background_image_timestamp = {
                name: wantedStamp,
                date: inDb
            };

but it still doesn't work ...

6
  • See here Commented Jun 21, 2022 at 6:29
  • does the key have a unique constraint? if not, add one Commented Jun 21, 2022 at 7:13
  • Does this answer your question? No unique or exclusion constraint matching the ON CONFLICT Commented Jun 21, 2022 at 7:14
  • @YashKumarVerma it did not sadly ... Commented Jun 21, 2022 at 8:08
  • @YashKumarVerma how can i add unique constraint Commented Jun 21, 2022 at 8:16

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.