Concatinating attributes in PostgreSQL

Question

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?

always include your PostgreSQL version in your questions. You've seen why; you were given an answer, but you're using a two-versions-old PostgreSQL that didn't have the feature, wasting everyone's time following up with a different approach. — Craig Ringer
– Craig Ringer, Commented Dec 14, 2012 at 0:43

Ihor Romanchenko · Accepted Answer · 2012-12-13 17:19:41Z

3

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
);

edited Dec 13, 2012 at 17:19

answered Dec 13, 2012 at 13:42

Ihor Romanchenko

29k9 gold badges56 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

theuserkaps Over a year ago

@ignor im using the earlier version 9.0

theuserkaps Over a year ago

mind if i ask what does the query SELECT $1 || (CASE WHEN $1 = '' THEN '' ELSE $3 END) || $2; do.....is $1 a variable??

Ihor Romanchenko Over a year ago

@theuserkaps $1 is the firs parameter of the function. $2 - second. You can name function parameters and use their names, or you can leave them without names and use $1, $2...

theuserkaps Over a year ago

thanks the function works though it is not sorting the result is there anywhere in the code where i can add orderby??

Ihor Romanchenko Over a year ago

@theuserkaps Try concat_delimited(num::text,'_') OVER (ORDER BY num)

|

Craig Ringer · Accepted Answer · 2012-12-14 00:45:45Z

1

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.

answered Dec 14, 2012 at 0:45

Craig Ringer

329k84 gold badges742 silver badges820 bronze badges

Comments

Robert H · Accepted Answer · 2012-12-13 13:38:15Z

-3

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.

edited Dec 13, 2012 at 13:38

answered Dec 13, 2012 at 13:30

Robert H

11.7k19 gold badges72 silver badges112 bronze badges

2 Comments

Mike Sherrill 'Cat Recall' Over a year ago

concat_ws() isn't an aggregate function.

Craig Ringer Over a year ago

... and you'd be looking for array_to_string rather than concat_ws for this job anyway.

Collectives™ on Stack Overflow

Concatinating attributes in PostgreSQL

3 Answers 3

10 Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

10 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related