title: array_append if element not exists
body: <blockquote>
    <p><strong>Possible Duplicate:</strong><br>
    <a href="https://stackoverflow.com/questions/8424283/convert-mysql-set-data-type-to-postgres">convert MySQL SET data type to Postgres</a>  </p>
  </blockquote>
  
  
  
  <p>I have postgresql 8.4.</p>
  
  <p>I am trying to build up a query without success. I would like to insert elements into an array if they are not exists in the conserning row.</p>
  
  <p>TABLE "test":</p>
  
  <pre><code>+------------------+------------------+
  | id               | varchar(20)      |
  | timestp          | timestamp        |
  | place            | varchar(20)      |
  | myarray[][]      | varchar[14][254] |
  +------------------+------------------+
  
  +---------+---------------------+-----------+----------------+
  |  xyz    | 2010-01-01 10:10:10 | nowhere   |  "{abc,qwert}" |
  +---------+---------------------+-----------+----------------+
  </code></pre>
  
  <p>So I would like to add "{fgh,gfdsa}" to myarray WHERE (id='xyz' AND timestp="2010-01-01 10:10:10" AND place='nowhere') <strong>IF it is not already in myarray</strong></p>
  
  <p>I tried something like this></p>
  
  <pre><code>UPDATE test
  SET myarray = array_append(wascon2,'{fgh,gfdsa}') 
  WHERE (id='xyz' AND timestp="2010-01-01 10:10:10" AND place='nowhere'
  AND NOT EXISTS 1 FROM test WHERE (myarray='{fgh,gfdsa}')));
  </code></pre>
  
  <p>This doesnt work, it will append the array every time when I call it.</p>
  
  <pre><code>+---+---------------------------+-------------------------------------------+
  |xyz|2010-01-01 10:10:10|nowhere|{"{abc,qwert}","{fgh,gfdsa}","{fgh,gfdsa}"}|
  +---+---------------------------+-------------------------------------------+
  </code></pre>
  
  <p>If think I should use ></p>
  
  <pre><code>myarray = ANY ('{fgh,gfds}'::varchar[]) 
  </code></pre>
  
  <p>somehow but I cant find out how..</p>
  
  <p>The best wolud be a plpgsql function for me.</p>