1

This question already has an answer here:

How to perform update and insert in same query? i need to update the record, if it is not present then it has to perform insertion i tried this

UPDATE bank SET  Address= $2,"PANCard"= $3,"IFSC" = $4  WHERE user_id = $1;
INSERT INTO bank (user_id,bank_details,"PAN",bank_acc,"UAN",tax)
       SELECT  '$2','$3','$4','$5','$6'
       WHERE NOT EXISTS (SELECT * FROM bank WHERE user_id=$1);

but am getting

{
  "name": "error",
  "length": 123,
  "severity": "ERROR",
  "code": "42601",
  "file": "postgres.c",
  "line": "1274",
  "routine": "exec_parse_message"
}

please help me here

marked as duplicate by a_horse_with_no_name postgresql May 24 '17 at 13:11

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

3

You can use the UPSERT:

https://www.postgresql.org/docs/9.5/static/sql-insert.html

Example from documentation:

INSERT INTO distributors (did, dname)
VALUES (5, 'Gizmo Transglobal'), (6, 'Associated Computing, Inc')
ON CONFLICT (did) DO UPDATE SET dname = EXCLUDED.dname;
  • i need to update the row, if the row does not exist then it should insert new one but with the above query new rows are inserted even if is already present. :( – Schüler May 24 '17 at 7:44
  • i gone through the doc, as per the doc unique index is required but in my query m using user_id as reference it is not unique, if the details is present for this user then it has to do update operation else insert – Schüler May 24 '17 at 9:46
3

Error code 42601 is "syntax_error", that's the first clue.

You are inserting into 6 columns: user_id,bank_details,"PAN",bank_acc,"UAN",tax, but your SELECT clause has only 5 columns: '$2','$3','$4','$5','$6'.

I suggest you try your SQL in psql or some other tool that give more helpful error messages:

create table foo(a int, b int);
insert into foo(a,b) values(1);
ERROR:  INSERT has more target columns than expressions
LINE 1: insert into foo(a,b) values(1);

dbfiddle here

  • i have added one more column to my select clause by still no use, does version plays a major role in this query ? am using version 9.6 – Schüler May 24 '17 at 7:09
  • 1
    "still no use" is not an error message. Once you've fixed the syntax, as you are on 9.6, use UPSERT as suggested by @user_0. – Jack Douglas May 24 '17 at 7:22
  • yep i tried UPSERT am getting { "name": "error", "length": 148, "severity": "ERROR", "code": "42P10", "file": "plancat.c", "line": "771", "routine": "infer_arbiter_indexes" } – Schüler May 24 '17 at 7:36
  • @Schüler search for the error code here: postgresql.org/docs/9.6/static/errcodes-appendix.html And then try to fix it yourself. – dezso May 24 '17 at 9:15

Not the answer you're looking for? Browse other questions tagged or ask your own question.