我有一个对象表,其中包含多个属性的列和一个具有唯一 SERIAL 标识符的列。
例如:
CREATE TABLE person(
id SERIAL NOT NULL,
name VARCHAR(16) NOT NULL,
age INT NOT NULL,
can_drive BOOL NOT NULL
)
现在,是否可以编写一个查询,检查某个条目(例如:Fred, 27, true)是否在表中,如果是,则返回 id,否则插入条目并返回新 id ?
try:
t=# with i as (
insert into person (name,age,can_drive)
select 'Fred',27,true
where not exists (
select 0 from person where name = 'Fred' and age= 27 and can_drive
)
returning id
)
select * from i
union all
select id from person where name = 'Fred' and age= 27 and can_drive;
id
----
1
(1 row)
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)