Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2008
    Posts
    1

    Unanswered: How convert array elements to rows?

    I need to convert array elements to rows. Suppose that I have the array {5,77,39,19}. I need it as a table with the index in one column and the element in the other column:
    Code:
    index | element
    ------+--------
        1 |       5
        2 |      77
        3 |      39
        4 |      19
    Which command does this?

  2. #2
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    If index will increment to 5 with the next array you process, just create your table with a serial datatype for index.

    Then, use the generate_series function to return all elements of the array, as part of a subquery for an insert.

    I haven't tested this, but something along this line may work

    Code:
    Insert Into YourNewTable (Element) select YourArrayColumn[generate_series(1,4)] as Element From YourExistingTable
    This assumes that you'll always have 4 elements per array. If not, you'll need to use the array function which returns the upper bound of the array as well, as a subquery to the subquery. (I don't remember the array function name - check the documentation for it...)
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •