Thursday, April 21, 2011

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 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.


Post a Comment