Postgres Pro
Downloads
Home   >   mailing lists

Thread: Concatenation through SQL

Concatenation through SQL

From
"Philippe Lang"
Date:
Hi,

Here is small reduced test database:

------------------------------
CREATE TABLE appointments
( id integer, name varchar(32), CONSTRAINT appointments_pkey PRIMARY KEY (id)
);

CREATE TABLE employees
( id integer, appointment_id integer, name varchar(32), CONSTRAINT employees_pkey PRIMARY KEY (id), CONSTRAINT
appointments_employees_fkeyFOREIGN KEY (appointment_id) 
REFERENCES appointments (id)
);

INSERT INTO appointments VALUES (1, 'app1');
INSERT INTO employees VALUES (1, 1, 'emp1');
INSERT INTO employees VALUES (2, 1, 'emp2');
INSERT INTO employees VALUES (3, 1, 'emp3');

INSERT INTO appointments VALUES (2, 'app2');
INSERT INTO employees VALUES (4, 2, 'emp1');
INSERT INTO employees VALUES (5, 2, 'emp4');
------------------------------


I'm trying to write an SQL query that would return this:

---------------------------------------------------
appointment    count_employees    employees
---------------------------------------------------
app1           3                  emp1, emp2, emp3
app2           2                  emp1, emp4,
---------------------------------------------------

First part is easy to write:

------------------------------
SELECT
appointments.name AS appointment,
(SELECT COUNT(*) FROM employees AS e where e.appointment_id =
appointments.id) AS num_employees
FROM appointments
------------------------------

... But concatenating employees name is harder, at least for me...

I'm convinced this can be done with Set Returning Functions and a bit of
plpgsql, but I was wondering if it would possible to write some kind of
extension to Postgresql (operator?) that would allow this kind of
syntax:

------------------------------
SELECT
appointments.name AS appointment,
(SELECT COUNT(*) FROM employees AS e where e.appointment_id =
appointments.id) AS num_employees
(SELECT CONCAT(name, ', ') FROM employees AS e where e.appointment_id =
appointments.id) AS employees
FROM appointments
------------------------------

... where CONCAT suggest we want to concatenate the variable inside,
with the separator ', ' inbetween.

Thanks for your tips!


Philippe Lang

Re: Concatenation through SQL

From
Andreas Kretschmer
Date:
Philippe Lang <philippe.lang@attiksystem.ch> schrieb:

2 ways:

* You can use something like this:

test=*# select * from a;id | val
----+----- 1 | foo 2 | bar
(2 rows)

test=*# select array_to_string(array(select val from a), ', ');array_to_string
-----------------foo, bar
(1 row)


* comma-aggregate, see
http://www.zigo.dhs.org/postgresql/#comma_aggregate


Andreas
-- 
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."    (unknow)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°

Re: Concatenation through SQL

From
"Pavel Stehule"
Date:
Hello

postgres=# select name, count, employes from  (select appointment_id,
count(*), (SELECT array_to_string(ARRAY(SELECT name from employees
where appointment_id = e.appointment_id),',')) as employes from
employees e group by appointment_id)s join appointments a on a.id =
s.appointment_id;name | count |    employes
------+-------+----------------app2 |     2 | emp1,emp4app1 |     3 | emp1,emp2,emp3
(2 rows)

regards
Pavel Stehule

for longer table is better define own aggregate function.


On 21/12/2007, Philippe Lang <philippe.lang@attiksystem.ch> wrote:
> Hi,
>
> Here is small reduced test database:
>
> ------------------------------
> CREATE TABLE appointments
> (
>   id integer,
>   name varchar(32),
>   CONSTRAINT appointments_pkey PRIMARY KEY (id)
> );
>
> CREATE TABLE employees
> (
>   id integer,
>   appointment_id integer,
>   name varchar(32),
>   CONSTRAINT employees_pkey PRIMARY KEY (id),
>   CONSTRAINT appointments_employees_fkey FOREIGN KEY (appointment_id)
> REFERENCES appointments (id)
> );
>
> INSERT INTO appointments VALUES (1, 'app1');
> INSERT INTO employees VALUES (1, 1, 'emp1');
> INSERT INTO employees VALUES (2, 1, 'emp2');
> INSERT INTO employees VALUES (3, 1, 'emp3');
>
> INSERT INTO appointments VALUES (2, 'app2');
> INSERT INTO employees VALUES (4, 2, 'emp1');
> INSERT INTO employees VALUES (5, 2, 'emp4');
> ------------------------------
>
>
> I'm trying to write an SQL query that would return this:
>
> ---------------------------------------------------
> appointment    count_employees    employees
> ---------------------------------------------------
> app1           3                  emp1, emp2, emp3
> app2           2                  emp1, emp4,
> ---------------------------------------------------
>
> First part is easy to write:
>
> ------------------------------
> SELECT
> appointments.name AS appointment,
> (SELECT COUNT(*) FROM employees AS e where e.appointment_id =
> appointments.id) AS num_employees
> FROM appointments
> ------------------------------
>
> ... But concatenating employees name is harder, at least for me...
>
> I'm convinced this can be done with Set Returning Functions and a bit of
> plpgsql, but I was wondering if it would possible to write some kind of
> extension to Postgresql (operator?) that would allow this kind of
> syntax:
>
> ------------------------------
> SELECT
> appointments.name AS appointment,
> (SELECT COUNT(*) FROM employees AS e where e.appointment_id =
> appointments.id) AS num_employees
> (SELECT CONCAT(name, ', ') FROM employees AS e where e.appointment_id =
> appointments.id) AS employees
> FROM appointments
> ------------------------------
>
> ... where CONCAT suggest we want to concatenate the variable inside,
> with the separator ', ' inbetween.
>
> Thanks for your tips!
>
>
> Philippe Lang
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: You can help support the PostgreSQL project by donating at
>
>                 http://www.postgresql.org/about/donate
>

Re: Concatenation through SQL

From
"Leif B. Kristensen"
Date:
On Friday 21. December 2007, Philippe Lang wrote:

>(SELECT CONCAT(name, ', ') FROM employees AS e where e.appointment_id
> = appointments.id) AS employees
>FROM appointments
>------------------------------
>
>... where CONCAT suggest we want to concatenate the variable inside,
>with the separator ', ' inbetween.

I've got a similar problem. My persons table has a number of fields for 
different name parts: given, patronym, toponym, surname, occupation, 
epithet. I've written a Plpgsql function that concatenates the full 
name, but it's big and ugly. I'd like something more elegant, like the 
Python or PHP join() function. I tried Andreas' suggestion like this:

pgslekt=> select array_to_string(array(select given, patronym, toponym 
from persons where person_id=57), ' ');
ERROR:  subquery must return only one column

Is there any way to accomplish this from Plpgsql?
-- 
Leif Biberg Kristensen | Registered Linux User #338009
http://solumslekt.org/ | Cruising with Gentoo/KDE
My Jazz Jukebox: http://www.last.fm/user/leifbk/

Re: Concatenation through SQL

From
imad
Date:
On Dec 21, 2007 4:16 PM, Leif B. Kristensen <leif@solumslekt.org> wrote:
> On Friday 21. December 2007, Philippe Lang wrote:
>
> >(SELECT CONCAT(name, ', ') FROM employees AS e where e.appointment_id
> > = appointments.id) AS employees
> >FROM appointments
> >------------------------------
> >
> >... where CONCAT suggest we want to concatenate the variable inside,
> >with the separator ', ' inbetween.
>
> I've got a similar problem. My persons table has a number of fields for
> different name parts: given, patronym, toponym, surname, occupation,
> epithet. I've written a Plpgsql function that concatenates the full
> name, but it's big and ugly. I'd like something more elegant, like the
> Python or PHP join() function. I tried Andreas' suggestion like this:
>
> pgslekt=> select array_to_string(array(select given, patronym, toponym
> from persons where person_id=57), ' ');
> ERROR:  subquery must return only one column

Try concatenating your columns using the '||' operator or 'concat' function
and project only one column from the subquery. You won't need to convert it
to an array and back to string.


--Imad
Database Expert

Re: Concatenation through SQL

From
Niklas Johansson
Date:
On 21 dec 2007, at 12.16, Leif B. Kristensen wrote:
> I've got a similar problem. My persons table has a number of fields  
> for
> different name parts: given, patronym, toponym, surname, occupation,
> epithet.

> I'd like something more elegant, like the
> Python or PHP join() function. I tried Andreas' suggestion like this:
>
> pgslekt=> select array_to_string(array(select given, patronym, toponym
> from persons where person_id=57), ' ');
> ERROR:  subquery must return only one column

Your case is not the same as Philippe's, since you have the values to  
be concatenated in columns, whereas he had them in rows.

However, something like this would probably achieve what you're  
looking for:

select array_to_string(array[given, patronym, toponym], ' ') from  
persons where person_id=57;

Notice the use of the array[] constructor, instead of the array()  
constructor, which must be fed a subquery which returns only one column.

Re: Concatenation through SQL

From
"Leif B. Kristensen"
Date:
On Friday 21. December 2007, Niklas Johansson wrote:

>select array_to_string(array[given, patronym, toponym], ' ') from
>persons where person_id=57;
>
>Notice the use of the array[] constructor, instead of the array()
>constructor, which must be fed a subquery which returns only one
> column.

Aah, great!

pgslekt=> select array_to_string(array[given, patronym, toponym], ' ')
from persons where person_id=57;    array_to_string
--------------------------Abraham Jonsen Bjørntvet
(1 row)

Tackar och bockar!
--
Leif Biberg Kristensen | Registered Linux User #338009
http://solumslekt.org/ | Cruising with Gentoo/KDE
My Jazz Jukebox: http://www.last.fm/user/leifbk/

Re: Concatenation through SQL

From
"Philippe Lang"
Date:
pgsql-sql-owner@postgresql.org wrote:
> Philippe Lang <philippe.lang@attiksystem.ch> schrieb:
>
> 2 ways:
>
> * You can use something like this:
>
> test=*# select * from a;
>  id | val
> ----+-----
>   1 | foo
>   2 | bar
> (2 rows)
>
> test=*# select array_to_string(array(select val from a), ', ');
> array_to_string -----------------
>  foo, bar
> (1 row)
>
>
> * comma-aggregate, see
> http://www.zigo.dhs.org/postgresql/#comma_aggregate

Hi,

Thanks to all who responded, in particular Andreas. I'm always amazed by
the quality of both the Postgresql database and the support in its
newsgroups.

The "comma-aggregate" worked just fine for me. Here is the final
example, for those willing to test it. This will be more than useful!

---------------------------------------
CREATE TABLE appointments
( id integer, name varchar(32), CONSTRAINT appointments_pkey PRIMARY KEY (id)
);

CREATE TABLE employees
( id integer, appointment_id integer, name varchar(32), CONSTRAINT employees_pkey PRIMARY KEY (id), CONSTRAINT
appointments_employees_fkeyFOREIGN KEY (appointment_id) 
REFERENCES appointments (id)
);

INSERT INTO appointments VALUES (1, 'app1');
INSERT INTO employees VALUES (1, 1, 'emp1');
INSERT INTO employees VALUES (2, 1, 'emp2');
INSERT INTO employees VALUES (3, 1, 'emp3');

INSERT INTO appointments VALUES (2, 'app2');
INSERT INTO employees VALUES (4, 2, 'emp1');
INSERT INTO employees VALUES (5, 2, 'emp4');

CREATE AGGREGATE array_accum (anyelement)
(  sfunc = array_append,  stype = anyarray,  initcond = '{}'
);

SELECT
appointments.name AS appointment,
(SELECT COUNT(*) FROM employees AS e where e.appointment_id =
appointments.id) AS num_employees,
(SELECT array_to_string(array_accum(name),', ') FROM employees AS e
WHERE e.appointment_id = appointments.id) AS employees
FROM appointments
---------------------------------------


Result:

---------------------------------------------------
appointment    count_employees    employees
---------------------------------------------------
app1           3                  emp1, emp2, emp3
app2           2                  emp1, emp4,
---------------------------------------------------


Merry christmas to all.

Philippe

Re: Concatenation through SQL

From
"Pavel Stehule"
Date:
On 21/12/2007, Philippe Lang <philippe.lang@attiksystem.ch> wrote:
> pgsql-sql-owner@postgresql.org wrote:
> > Philippe Lang <philippe.lang@attiksystem.ch> schrieb:
> >
> > 2 ways:
> >
> > * You can use something like this:
> >
> > test=*# select * from a;
> >  id | val
> > ----+-----
> >   1 | foo
> >   2 | bar
> > (2 rows)
> >
> > test=*# select array_to_string(array(select val from a), ', ');
> > array_to_string -----------------
> >  foo, bar
> > (1 row)
> >
> >
> > * comma-aggregate, see
> > http://www.zigo.dhs.org/postgresql/#comma_aggregate
>
> Hi,
>
> Thanks to all who responded, in particular Andreas. I'm always amazed by
> the quality of both the Postgresql database and the support in its
> newsgroups.
>
> The "comma-aggregate" worked just fine for me. Here is the final
> example, for those willing to test it. This will be more than useful!
>
> ---------------------------------------
> CREATE TABLE appointments
> (
>   id integer,
>   name varchar(32),
>   CONSTRAINT appointments_pkey PRIMARY KEY (id)
> );
>
> CREATE TABLE employees
> (
>   id integer,
>   appointment_id integer,
>   name varchar(32),
>   CONSTRAINT employees_pkey PRIMARY KEY (id),
>   CONSTRAINT appointments_employees_fkey FOREIGN KEY (appointment_id)
> REFERENCES appointments (id)
> );
>
> INSERT INTO appointments VALUES (1, 'app1');
> INSERT INTO employees VALUES (1, 1, 'emp1');
> INSERT INTO employees VALUES (2, 1, 'emp2');
> INSERT INTO employees VALUES (3, 1, 'emp3');
>
> INSERT INTO appointments VALUES (2, 'app2');
> INSERT INTO employees VALUES (4, 2, 'emp1');
> INSERT INTO employees VALUES (5, 2, 'emp4');
>
> CREATE AGGREGATE array_accum (anyelement)
> (
>   sfunc = array_append,
>   stype = anyarray,
>   initcond = '{}'
> );
>
> SELECT
> appointments.name AS appointment,
> (SELECT COUNT(*) FROM employees AS e where e.appointment_id =
> appointments.id) AS num_employees,
> (SELECT array_to_string(array_accum(name),', ') FROM employees AS e
> WHERE e.appointment_id = appointments.id) AS employees
> FROM appointments
> ---------------------------------------
>

hello,

one note: you can merge your two subselect into one. It's unimportant
on 100 rows, but it's can be important on thousands rows.

nice a day
Pavel

>
> Result:
>
> ---------------------------------------------------
> appointment    count_employees    employees
> ---------------------------------------------------
> app1           3                  emp1, emp2, emp3
> app2           2                  emp1, emp4,
> ---------------------------------------------------
>
>
> Merry christmas to all.
>
> Philippe
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>        choose an index scan if your joining column's datatypes do not
>        match
>

Re: Concatenation through SQL

From
"Philippe Lang"
Date:
Pavel Stehule wrote:
>> SELECT
>> appointments.name AS appointment,
>> (SELECT COUNT(*) FROM employees AS e where e.appointment_id =
>> appointments.id) AS num_employees,
>> (SELECT array_to_string(array_accum(name),', ') FROM employees AS e
>> WHERE e.appointment_id = appointments.id) AS employees FROM
>> appointments ---------------------------------------
>>
>
> hello,
>
> one note: you can merge your two subselect into one. It's unimportant
> on 100 rows, but it's can be important on thousands rows.
>
> nice a day
> Pavel

Hi Pavel,

Since subselects must return one single column, is that what you are
talking about? I have just modified the code you have posted before.

---------------------------
SELECT  name AS appointment,  num_employees,  employees

FROM
( SELECT    appointment_id,    COUNT(*) AS num_employees,    (SELECT array_to_string(array_accum(name),', ') FROM
employeesWHERE 
e.appointment_id = appointment_id) AS employees FROM employees e  GROUP BY appointment_id
)s

JOIN appointments a on a.id = s.appointment_id;
---------------------------

Philippe