Remove array values in pgSQL

Is there a way to remove a value from an array in pgSQL? Or to be more precise, to pop the last value? Judging by this list the answer seems to be no. I can get the result I want with an additional index pointer, but it's a bit cumbersome.

From stackoverflow

• No, I don't think you can. At least not without writing something ugly like:

  SELECT ARRAY (
   SELECT UNNEST(yourarray) LIMIT (
    SELECT array_upper(yourarray, 1) - 1
   )
  )
  

  oggy : Judging from what Google tells me, it seems that I can't. I'll mark this as the accepted answer unless somebody proves you wrong :)

• I'm not sure about your context, but this should give you something to work with:

  CREATE TABLE test (x INT[]);
  INSERT INTO test VALUES ('{1,2,3,4,5}');
  
  SELECT x AS array_pre_pop,
         x[array_lower(x,1) : array_upper(x,1)-1] AS array_post_pop, 
         x[array_upper(x,1)] AS popped_value 
  FROM test;
  
  
   array_pre_pop | array_post_pop | popped_value 
  ---------------+----------------+--------------
   {1,2,3,4,5}   | {1,2,3,4}      |            5
  

  oggy : Thanks, that would work though I guess slicing isn't exactly an efficient solution?

  Matthew Wood : I think it's your best method considering there's no built-in function for pop(). Without knowing the specifics, I can't give better advice. If you want to loop through the contents for a particular record, then unnest() would probably be better as it would convert into a set of records. However, if you just want to update a table to remove all the "last elements" of the array in multiple records, array slicing would be the way to go.