title: PostgreSQL: duplicate key value violates unique constraint on UPDATE command
body: <p>When doing an UPDATE query, we got the following error message:</p>
  
  <pre><code>ERROR:  duplicate key value violates unique constraint "tableA_pkey"
  DETAIL:  Key (id)=(47470) already exists.
  </code></pre>
  
  <p>However, our UPDATE query does not affect the primary key. Here is a simplified version:</p>
  
  <pre><code>UPDATE tableA AS a
  SET
      items = (
          SELECT array_to_string(
              array(
                  SELECT b.value
                  FROM tableB b
                  WHERE b.a_id = b.id
                  GROUP BY b.name
              ),
              ','
          )
      )
  WHERE
      a.end_at BETWEEN now() AND  now() - interval '1 day';
  </code></pre>
  
  <p>We ensured the primary key sequence was already synced:</p>
  
  <pre><code>\d tableA_id_seq
  </code></pre>
  
  <p>Which produces:</p>
  
  <pre><code>    Column     |  Type   |          Value           
  ---------------+---------+--------------------------
   sequence_name | name    | tableA_id_seq
   last_value    | bigint  | 50364
   start_value   | bigint  | 1
   increment_by  | bigint  | 1
   max_value     | bigint  | 9223372036854775807
   min_value     | bigint  | 1
   cache_value   | bigint  | 1
   log_cnt       | bigint  | 0
   is_cycled     | boolean | f
   is_called     | boolean | t
  </code></pre>
  
  <p>Looking for maximum table index:</p>
  
  <pre><code>select max(id) from tableA;
  </code></pre>
  
  <p>We got a lower value:</p>
  
  <pre><code>  max  
  -------
   50363
  (1 row)
  </code></pre>
  
  <p>Have you any idea on why such a behavior? If we exclude the problematic id, it works.</p>
  
  <p>Another strange point is that replacing the previous UPDATE by:</p>
  
  <pre><code>UPDATE tableA AS a
  SET
      items = (
          SELECT array_to_string(
              array(
                  SELECT b.value
                  FROM tableB b
                  WHERE b.a_id = b.id
                  GROUP BY b.name
              ),
              ','
          )
      )
  WHERE a.id = 47470;
  </code></pre>
  
  <p>It works well. Are we missing something?</p>
  
  <p><strong>EDIT:</strong> triggers</p>
  
  <p>I have no user-defined triggers on this table:</p>
  
  <pre><code>SELECT t.tgname, c.relname
  FROM pg_trigger t
  JOIN pg_class c ON t.tgrelid = c.oid
  WHERE
      c.relname = 'tableA'
      AND
      t.tgisinternal = false
  ;
  </code></pre>
  
  <p>Which returns no row.</p>
  
  <p><strong>Note:</strong> I am using <em>psql (PostgreSQL) 9.3.4</em> version.</p>