How to Concatenate Field Values in PostgreSQL?

Join field values ​​in PostgreSQL

In some database applications, it is necessary to retrieve a single row for each unique identifier from the table and concatenate the corresponding field values. For example, given a table containing the following data:

ID	列A	列B
TM67	4	32556
TM67	9	98200
TM67	72	22300
TM99	2	23009
TM99	3	11200

The expected output is as follows:

ID	列A	列B
TM67	4,9,72	32556,98200,22300
TM99	2,3	23009,11200

In MySQL, the GROUP_CONCAT aggregate function is usually used to achieve this purpose. However, there is no direct equivalent in PostgreSQL.

Fortunately, PostgreSQL provides some alternative mechanisms to achieve the same result:

<code class="language-sql">SELECT id, 
       string_agg(some_column, ',')
FROM the_table
GROUP BY id</code>

This method uses the string_agg function (introduced in PostgreSQL 9.0) to aggregate the values ​​of the specified column into a comma-delimited string. The GROUP BY clause ensures that the aggregation operation is performed separately for each unique ID.

The above is the detailed content of How to Concatenate Field Values in PostgreSQL?. For more information, please follow other related articles on the PHP Chinese website!