How to concatenate strings of a string field in a PostgreSQL 'group by' query?
postgresql concatenate rows
postgres cast to string
concatenate variable postgres
postgresql split string
concat fields in postgres
I am looking for a way to concatenate the strings of a field within a group by query. So for example, I have a table:
ID COMPANY_ID EMPLOYEE 1 1 Anna 2 1 Bill 3 2 Carol 4 2 Dave
and I wanted to group by company_id to get something like:
COMPANY_ID EMPLOYEE 1 Anna, Bill 2 Carol, Dave
There is a built-in function in mySQL to do this group_concat
PostgreSQL 9.0 or later:
Recent versions of Postgres (since late 2010) have the
string_agg(expression, delimiter) function which will do exactly what the question asked for, even letting you specify the delimiter string:
SELECT company_id, string_agg(employee, ', ') FROM mytable GROUP BY company_id;
Postgres 9.0 also added the ability to specify an
ORDER BY clause in any aggregate expression; otherwise, the order is undefined. So you can now write:
SELECT company_id, string_agg(employee, ', ' ORDER BY employee) FROM mytable GROUP BY company_id;
SELECT string_agg(actor_name, ', ' ORDER BY first_appearance)
PostgreSQL 8.4 or later:
PostgreSQL 8.4 (in 2009) introduced the aggregate function
array_agg(expression) which concatenates the values into an array. Then
array_to_string() can be used to give the desired result:
SELECT company_id, array_to_string(array_agg(employee), ', ') FROM mytable GROUP BY company_id;
string_agg for pre-8.4 versions:
In case anyone comes across this looking for a compatibilty shim for pre-9.0 databases, it is possible to implement everything in
string_agg except the
ORDER BY clause.
So with the below definition this should work the same as in a 9.x Postgres DB:
SELECT string_agg(name, '; ') AS semi_colon_separated_names FROM things;
But this will be a syntax error:
SELECT string_agg(name, '; ' ORDER BY name) AS semi_colon_separated_names FROM things; --> ERROR: syntax error at or near "ORDER"
Tested on PostgreSQL 8.3.
CREATE FUNCTION string_agg_transfn(text, text, text) RETURNS text AS $$ BEGIN IF $1 IS NULL THEN RETURN $2; ELSE RETURN $1 || $3 || $2; END IF; END; $$ LANGUAGE plpgsql IMMUTABLE COST 1; CREATE AGGREGATE string_agg(text, text) ( SFUNC=string_agg_transfn, STYPE=text );
Custom variations (all Postgres versions)
Prior to 9.0, there was no built-in aggregate function to concatenate strings. The simplest custom implementation (suggested by Vajda Gabo in this mailing list post, among many others) is to use the built-in
textcat function (which lies behind the
CREATE AGGREGATE textcat_all( basetype = text, sfunc = textcat, stype = text, initcond = '' );
This simply glues all the strings together, with no separator. In order to get a ", " inserted in between them without having it at the end, you might want to make your own concatenation function and substitute it for the "textcat" above. Here is one I put together and tested on 8.3.12:
CREATE FUNCTION commacat(acc text, instr text) RETURNS text AS $$ BEGIN IF acc IS NULL OR acc = '' THEN RETURN instr; ELSE RETURN acc || ', ' || instr; END IF; END; $$ LANGUAGE plpgsql;
This version will output a comma even if the value in the row is null or empty, so you get output like this:
a, b, c, , e, , g
If you would prefer to remove extra commas to output this:
a, b, c, e, g
Then add an
ELSIF check to the function like this:
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;
PostgreSQL CONCAT Function By Practical Examples, Introduction to PostgreSQL CONCAT function. To concatenate two or more strings into one, you use the string concatenation operator || as the following example Following yet again on the use of a custom aggregate function of string concatenation: you need to remember that the select statement will place rows in any order, so you will need to do a sub select in the from statement with an order by clause, and then an outer select with a group by clause to aggregate the strings, thus:
How about using Postgres built-in array functions? At least on 8.4 this works out of the box:
SELECT company_id, array_to_string(array_agg(employee), ',') FROM mytable GROUP BY company_id;
PostgreSQL CONCAT() function, In the example below the strings specified in the argument have been concatenated and returns a string. SELECT concat('w',3,'r', Example of PostgreSQL CONCAT() function using column : Sample Table: employees. PostgreSQL concatenate using comma separator example. If you’d like to use a comma as a separator between two strings, use the syntax shown in this example: The CONCAT_WS () function call used in this example allows you to pass a special separator or delimiter, such as a comma, when concatenating strings.
As from PostgreSQL 9.0 you can use the aggregate function called string_agg. Your new SQL should look something like this:
SELECT company_id, string_agg(employee, ', ') FROM mytable GROUP BY company_id;
Documentation: 9.1: String Functions and Operators, However, the string concatenation operator (||) still accepts non-string input, field int), text, Split string on delimiter and return the given field (counting from one) The STRING_AGG() function in PostgreSQL is an aggregate function that is used to concatenate a list of strings and place a separator between them. Syntax: STRING_AGG ( expression, separator [order_by_clause] ) Let’s analyze the above syntax. The above function accepts two arguments and an optional ORDER BY clause.
I claim no credit for the answer because I found it after some searching:
What I didn't know is that PostgreSQL allows you to define your own aggregate functions with CREATE AGGREGATE
This post on the PostgreSQL list shows how trivial it is to create a function to do what's required:
CREATE AGGREGATE textcat_all( basetype = text, sfunc = textcat, stype = text, initcond = '' ); SELECT company_id, textcat_all(employee || ', ') FROM mytable GROUP BY company_id;
Concatenate strings using GROUP BY, Concatenate strings using GROUP BY Is there a pure SQL way of contactenating str values into a single string, and get instead: id fk str PostgreSQL 8.4 (in 2009) introduced the aggregate function array_agg (expression) which concatenates the values into an array. Then array_to_string () can be used to give the desired result: 1. 2. 3.
As already mentioned, creating your own aggregate function is the right thing to do. Here is my concatenation aggregate function (you can find details in French):
CREATE OR REPLACE FUNCTION concat2(text, text) RETURNS text AS ' SELECT CASE WHEN $1 IS NULL OR $1 = \'\' THEN $2 WHEN $2 IS NULL OR $2 = \'\' THEN $1 ELSE $1 || \' / \' || $2 END; ' LANGUAGE SQL; CREATE AGGREGATE concatenate ( sfunc = concat2, basetype = text, stype = text, initcond = ''
And then use it as:
SELECT company_id, concatenate(employee) AS employees FROM ...
PostgreSQL: || Operator, This PostgreSQL tutorial explains how to use the PostgreSQL || concatenate operator The PostgreSQL || operator allows you to concatenate 2 or more strings together. string1: The first string to concatenate. string2: The second string to Example of PostgreSQL CONCAT() function using column : Sample Table: employees If we want to display the first_name, last_name, and Name of the employee for those employees who belongs to the department which ID is 100 from employees table the following statement can be executed.
Postgres Concat Function in SQL, The PostgreSQL CONCAT function combines two or more strings. from the “t_category” field (column) in our database, we know this string There was a string concatenation function in MySQL: SELECT CONCAT(first_name, " ", last_name) FROM table; Is there a similar function in Postgres? Certainly, it's possible to live without it, but i'd like to write as above,
SQL Server CONCAT Function By Practical Examples, In this tutorial, you will learn how to use the SQL Server CONCAT() function to join multiple strings into one string. 9.4. String Functions and Operators. This section describes functions and operators for examining and manipulating string values. Strings in this context include values of the types character, character varying, and text.
What's the best way to concatenate all the values of a column in , There is no group_concat function in PostgreSQL, but starting with version 8.4, you can use the array_agg() built-in. For example, you can do something like: Simpler with the aggregate function string_agg () (Postgres 9.0 or later): SELECT movie, string_agg(actor, ', ') AS actor_list FROM tbl GROUP BY 1; The 1 in GROUP BY 1 is a positional reference and a shortcut for GROUP BY movie in this case. string_agg () expects data type text as input.
- Markus Döring's answer is technically better.
- @pstanton, Döring's answer is only better for 8.4 and below.
- This question appears to be better suited for dba.stackexchange.com.
- This should be the valid answer now stackoverflow.com/a/47638417/243233
- I had to S&R varchar to text (latest pgsql stable) but this is great!
- You can write the function in SQL only, which is easier for installation (plpgsql has to be installed by the superuser). See my post for an example.
- "There is no built-in aggregate function to concatenate strings" - why wouldn't you use
- +1 for the PostgreSQL 9.0 function. If you need to be concerned about pre-9.0, Markus's answer is better.
- Note that recent versions of Postgres also allow an
Order Byclause inside the aggregate function, e.g.
string_agg(employee, ',' Order By employee)
- sadly this doesn't work for us on Greenplum (v8.2). +1 all the same
- Works fine for me on Greenplum 18.104.22.168 (built on PostgreSQL 8.2.15).
- How is this related to using an aggregate to concatenate string values?