17
Feb
2010
2

array_to_string in PostgreSQL

This snippet courtesy of Kieran Smith.

Want to get more work done in a single query? Tired of looping through query results to simply build a list of data. How about this?

SELECT
array_to_string(
    ARRAY(
        SELECT name
        FROM projects
        WHERE customer_id = 10
        ORDER BY LOWER(name)
    ), ', '
) AS projects;

What you are doing is building an array from the subquery and then turning it back into a string immediately, so you can display multiple results in a single field. Our results might look something like this:

gnucashweb, Hangar, OpenAvanti, tarmac

Nice!

Category: Database

2 Responses

  • Theory
    February 23, 2010 at 1:44 PM

    9.0 has the new `string_agg()` function. So then you’ll be able to just do:

    SELECT string_agg(name, ‘, ‘)
    FROM projects
    WHERE CUSTOMER_ID = 10
    ORDER BY LOWER(name);

    —Theory

  • Kristopher
    February 23, 2010 at 8:10 PM

    That’s awesome! Thanks for sharing, Theory.

Leave a Reply

Why ask?

Copyright © 2010 — phup 'n stuff | Site design by Trevor Fitzgerald