How to Concatenate Strings within a PostgreSQL `group by` Query?

Concatenate string fields in PostgreSQL group by query

When processing data in a PostgreSQL database, you may need to concatenate strings for specific fields in a group by query. This can be achieved in a variety of ways, depending on the version of PostgreSQL.

PostgreSQL 9.0 and higher

Modern PostgreSQL versions (released after 2010) provide the string_agg(expression, delimiter) function. This function allows concatenating strings in group by queries, separated by specified delimiters.

For example, to join the COMPANY_ID fields in each EMPLOYEE group, you can use the following query:

<code class="language-sql">SELECT company_id, string_agg(employee, ', ')
FROM mytable
GROUP BY company_id;</code>

PostgreSQL 8.4.x

In PostgreSQL 8.4, you can use array_agg(expression) aggregate functions to collect values ​​into an array. The array can then be converted into a concatenated string using the array_to_string() function:

<code class="language-sql">SELECT company_id, array_to_string(array_agg(employee), ', ')
FROM mytable
GROUP BY company_id;</code>

PostgreSQL 8.3.x and lower versions

In older PostgreSQL versions, there is no built-in aggregate function for string concatenation. One custom implementation involves creating a custom aggregate function using the textcat function:

<code class="language-sql">CREATE AGGREGATE textcat_all(
  basetype    = text,
  sfunc       = textcat,
  stype       = text,
  initcond    = ''
);</code>

Additionally, a custom connection function can be created to handle specific needs, such as ignoring null values ​​or empty strings:

<code class="language-sql">CREATE FUNCTION commacat_ignore_nulls(acc text, instr text) RETURNS text AS $$
  BEGIN
    IF acc IS NULL OR acc = '' THEN
      RETURN instr;
    ELSIF instr IS NULL OR instr = '' THEN
      RETURN acc;
    ELSE
      RETURN acc || ', ' || instr;
    END IF;
  END;
$$ LANGUAGE plpgsql;</code>

Using this function, you can modify the query as follows:

<code class="language-sql">SELECT company_id, commacat_ignore_nulls(employee)
FROM mytable
GROUP BY company_id;</code>

The above is the detailed content of How to Concatenate Strings within a PostgreSQL `group by` Query?. For more information, please follow other related articles on the PHP Chinese website!