Syntax error in where clause in postgresql

Question

 fl=s.executeUpdate("
insert into demi(rno,subcode,subname,intm,extm,crd,resultdate)
values(
  '13JG1A05A0',
  'RT22058',
  ' FREE OPEN SOURCE SOFTWARE(FOSS) LAB ',
  '20',
  '70',
  '2',
  'MAY 2015'
)
end where not exists(SELECT * FROM demi WHERE rn0 ='13JG105A0' AND subcode='RT22058')
");

I'm working in jsp with postgresql as backend, my IDE shows error in this statement. i want to insert a record into db after checking and making sure that no such record already exists

Is this statement correct, or am I trying a garbage code?

Please help, thanks in advance

=> end where not exists should probably be AND where not exists. — dhke
– dhke, Commented Apr 9, 2017 at 17:46

user330315 · Accepted Answer · 2017-04-10 13:35:41Z

1

The reason for your error message is that an INSERT statement does not allow a WHERE clause.

You can only add a where clause to a SELECT statement (or a DELETE or UPDATE statement)

So you would need to get rid of the VALUES clause and use the insert into .. select ... syntax:

insert into demi(rno,subcode,subname,intm,extm,crd,resultdate)
select '13JG1A05A0',
       'RT22058',
       ' FREE OPEN SOURCE SOFTWARE(FOSS) LAB ',
       '20',
       '70',
       '2',
       'MAY 2015'
where not exists (SELECT * 
                  FROM demi 
                  WHERE rn0='13JG105A0' 
                    AND subcode='RT22058');

However for the intended use case:

I'm trying to insert a particular record into db if and only if there exists no other record with same rno and subject code columns

there is a better alternative if you have a unique constraint on (rno, subcode) (which you should) - use the on conflict clause:

insert into demi (rno, subcode, subname, intm, extm, crd, resultdate)
values
 (
  '13JG1A05A0',
  'RT22058',
  ' FREE OPEN SOURCE SOFTWARE(FOSS) LAB ',
  '20',
  '70',
  '2',
  'MAY 2015'
)
on conflict (rno, subcode) do nothing;

Again: the on conflict will only work if you have a unique constraint (or index) on those two columns.

Unrelated, but:

you should specify each constant value with a literal matching the underlying data type. '20' is a character constant, 20 would be a number. Ìf intm, extm and crd re numbers, don't provide character values. Also if resultdate is a date column 'MAY 2015' won't work either.

answered Apr 10, 2017 at 13:35

user330315

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

5 Comments

Himaja Sidhireddi Over a year ago

thanq so much you saved my day it worked with that i have a doubt i want do some action if the conflict arise where am i supposed to write that code

user330315 Over a year ago

@HimajaSidhireddi: no code required. The do nothing causes the offending row to be silently be ignored and the row will not be inserted.

Himaja Sidhireddi Over a year ago

but i need to write i have 2 tables demi and supply .if a record exists in demi i need to update record in demi if only current value in crd is greater than previous one .then copy previous record to supply so i need to write code for all this and will "on conflict insert" work? plz help

user330315 Over a year ago

You have not mentioned anything about a second table in your question. Changing the scope of the question after you have received an answer is considered rude. It's better to ask a new question with the additional requirements (you can reference this one). Or at least edit your current question to reflect the extended scope of your problem.

Himaja Sidhireddi Over a year ago

sorry for inconvenience i'm posting a qstn for first tym i'm going to ask a new qstn and thanq it worked for update and do nothing

Evan Carroll · Accepted Answer · 2017-04-10 00:11:05Z

0

Not sure how

end where not exists(SELECT * FROM demi WHERE rn0 ='13JG105A0' AND subcode='RT22058')

got into your code, but the whole thing is suspicious what do you even want that to do?

answered Apr 10, 2017 at 0:11

Evan Carroll

1

1 Comment

Himaja Sidhireddi Over a year ago

i'm trying to insert a particular record into db if and only if there exists no other record with same rno and subject code columns

Collectives™ on Stack Overflow

Syntax error in where clause in postgresql

2 Answers 2

5 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related