PostgreSQL中where子句的语法错误 [英] Syntax error in where clause in postgresql

问题描述

 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')
");  

我正在jsp中使用postgresql作为后端，我的IDE在此语句中显示错误。
i要在检查并确保没有这样的记录后将记录插入db

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?

请先帮助，谢谢

推荐答案

您的原因错误消息是 INSERT 语句不允许 WHERE 子句。

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

您只能在 SELECT where 子句>语句（或 Delete 或 UPDATE 语句）

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

因此，您需要摆脱 VALUES 子句，并使用 insert插入.. select ... 语法：

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:

仅当不存在具有相同rno和主题代码列的其他记录时，我才尝试将特定记录插入db

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

如果对（rno，subcode）（$ -应该使用发生冲突子句：

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;

再次：发生冲突

不相关，但：

应使用与基础数据类型匹配的文字来指定每个常量值。 20 是字符常量， 20 是数字。如果是intm，extm和crd re数字，则不提供字符值。同样，如果结果日期是日期列'MAY 2015'也不会工作。

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.

这篇关于PostgreSQL中where子句的语法错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！