Postgres INSERT ON CONFLICT DO UPDATE vs INSERT or UPDATE : <p>I have <code>stock_price_alert</code> table with 3 columns. <code>stock_price_id</code> is <code>PRIMARY KEY</code> &amp; also <code>FOREIGN KEY</code> to other table. Table definition as below:</p>

<pre><code>create table stock_price_alert (
    stock_price_id integer references stock_price (id) on delete cascade not null,
    fall_below_alert boolean not null,
    rise_above_alert boolean not null,
    primary key (stock_price_id)
);
</code></pre>

<p>I need to either:</p>

<p>1) <code>INSERT</code> record if not exist</p>

<pre><code>-- query 1
INSERT INTO stock_price_alert (stock_price_id, fall_below_alert, rise_above_alert)
VALUES (1, true, false);
</code></pre>

<p>2) <code>UPDATE</code> record if exist</p>

<pre><code>-- query 2
UPDATE stock_price_alert SET
    fall_below_alert = true,
    rise_above_alert = false
WHERE stock_price_id = 1;
</code></pre>

<p>First I need to issue <code>SELECT</code> query on <code>stock_price_alert</code> table, in order to decide whether to perform query (1) or (2).</p>

<p>Postgres supports <code>INSERT INTO TABLE .... ON CONFLICT DO UPDATE ...</code>:</p>

<pre><code>-- query 3
INSERT INTO stock_price_alert (stock_price_id, fall_below_alert, rise_above_alert)
VALUES (1, true, false)
ON CONFLICT (stock_price_id) DO UPDATE SET
    fall_below_alert = EXCLUDED.fall_below_alert,
    rise_above_alert = EXCLUDED.rise_above_alert;
</code></pre>

<p>Instead of using query (1) or (2), can I always use query (3)? Then I don't need to issue <code>SELECT</code> query in prior &amp; it helps to simplify the code. </p>

<p>But I am wondering, which is the best practice? Will query (3) cause performance issue or unwanted side effect? Thanks.</p>
