在一个命令中选择或插入一行 [英] SELECT or INSERT a row in one command

问题描述

我使用的是PostgreSQL 9.0，我有一个表，其中只有一个人工键（自动递增序列）和另一个唯一键。 （是的，有此表的原因。:)）我想通过另一个键查找一个ID，或者，如果不存在，则将其插入：

I'm using PostgreSQL 9.0 and I have a table with just an artificial key (auto-incrementing sequence) and another unique key. (Yes, there is a reason for this table. :)) I want to look up an ID by the other key or, if it doesn't exist, insert it:

SELECT id
FROM mytable
WHERE other_key = 'SOMETHING'

然后，如果没有匹配项：

Then, if no match:

INSERT INTO mytable (other_key)
VALUES ('SOMETHING')
RETURNING id

问题：是否可以保存一轮回合通过在一条语句中同时完成这两项操作来访问数据库？如果这样的行不存在，我可以插入该行：

The question: is it possible to save a round-trip to the DB by doing both of these in one statement? I can insert the row if it doesn't exist like this:

INSERT INTO mytable (other_key)
SELECT 'SOMETHING'
WHERE NOT EXISTS (SELECT * FROM mytable WHERE other_key = 'SOMETHING')
RETURNING id

...，但是不提供现有行的ID。有任何想法吗？

... but that doesn't give the ID of an existing row. Any ideas? There is a unique constraint on other_key, if that helps.

推荐答案

您是否尝试过合并它？

编辑-这需要Postgres 9.1：

Edit - this requires Postgres 9.1:

create table mytable (id serial primary key, other_key varchar not null unique);

WITH new_row AS (
INSERT INTO mytable (other_key)
SELECT 'SOMETHING'
WHERE NOT EXISTS (SELECT * FROM mytable WHERE other_key = 'SOMETHING')
RETURNING *
)
SELECT * FROM new_row
UNION
SELECT * FROM mytable WHERE other_key = 'SOMETHING';

结果：

 id | other_key 
----+-----------
  1 | SOMETHING
(1 row)

这篇关于在一个命令中选择或插入一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！