CODEWITHSUNDEEP
postgresqlconcatenationaggregate-functionsgroup-concat
theuserkaps
theuserkaps

Reputation: 308

Concatinating attributes in PostgreSQL

I am trying to create an aggregate function that concatenates numbers by grouping them. How can I go about it? Let's say I have a table like this below.

Table Numbers
123
145
187
105

I want the outcome to look like 

105_123_145_187

I know how to use group_concat separator _ if I am working in MySQL.

How can I do it in PostgreSQL?

Upvotes: 1

Views: 230

Answers (3)

Craig Ringer
Craig Ringer

Reputation: 324475

For modern PostgreSQL use string_agg(columnname,'_').

For old versions 8.4 and up, use string_to_array(array_agg(columname), '_')

See the array functions and operators documentation.

Example:

regress=> SELECT array_to_string(array_agg(x::text), ', ') FROM generate_series(1,10) x;
        array_to_string
-------------------------------
 1, 2, 3, 4, 5, 6, 7, 8, 9, 10
(1 row)

Always include your PostgreSQL version in your questions.

Upvotes: 1

Ihor Romanchenko
Ihor Romanchenko

Reputation: 28541

There is already such function:

SELECT string_agg(num::text,'_')
FROM Numbers;

Details here: string_agg.

Tell me, if you use postgresql 8.4 or earlier version. I will show you, how to implement this function as custom aggregate.

UPD Custom aggregate:

CREATE OR REPLACE FUNCTION public.concat_delimited (text, text, text)
RETURNS text AS
$body$
  SELECT $1 || (CASE WHEN $1 = '' THEN '' ELSE $3 END) || $2;
$body$
LANGUAGE 'sql'
IMMUTABLE
RETURNS NULL ON NULL INPUT;

CREATE AGGREGATE public.text_concat (text, text)
(
  SFUNC = public.concat_delimited,
  STYPE = text
);

Upvotes: 3

Robert H
Robert H

Reputation: 11730

concat_ws(sep text, str "any" [, str "any" [, ...] ]) is the function your looking for.

The first param is your separator, NULL args are ignored. See The PostgreSQL manual for details.

I am not versed in pgSQL at all, but the answer for writing an aggregate function is going to lay there, check out the pgSQL manual for how to write your functions.

Upvotes: -3

Related Questions