Return rows from INSERT with ON CONFLICT without needing to update
<p>I have a situation where I very frequently need to get a row from a table with a unique constraint, and if none exists then create it and return. 
For example my table might be:</p>

<pre><code>CREATE TABLE names(
    id SERIAL PRIMARY KEY,
    name TEXT,
    CONSTRAINT names_name_key UNIQUE (name)
);
</code></pre>

<p>And it contains:</p>

<pre><code>id | name
 1 | bob 
 2 | alice
</code></pre>

<p>Then I'd like to:</p>

<pre><code> INSERT INTO names(name) VALUES ('bob')
 ON CONFLICT DO NOTHING RETURNING id;
</code></pre>

<p>Or perhaps:</p>

<pre><code> INSERT INTO names(name) VALUES ('bob')
 ON CONFLICT (name) DO NOTHING RETURNING id
</code></pre>

<p>and have it return bob's id <code>1</code>. However, <code>RETURNING</code> only returns either inserted or updated rows. So, in the above example, it wouldn't return anything. In order to have it function as desired I would actually need to:</p>

<pre><code>INSERT INTO names(name) VALUES ('bob') 
ON CONFLICT ON CONSTRAINT names_name_key DO UPDATE
SET name = 'bob'
RETURNING id;
</code></pre>

<p>which seems kind of cumbersome. I guess my questions are:</p>

<ol>
<li><p>What is the reasoning for not allowing the (my) desired behaviour?</p></li>
<li><p>Is there a more elegant way to do this?</p></li>
</ol>