title: Postgres INSERT ON CONFLICT DO UPDATE vs INSERT or UPDATE
body: <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>