Concatenate columns into a single Postgresql row

Asked

Viewed 6,147 times

1

I have a situation that would make it much easier. if I could return the data from a specific column in a single row:

For example:

Table A:

Codigo | Descricao
-------------------
1      | Descricao 1
2      | Descricao 2
3      | Descricao 3

I would like to mount an sql that returns me in a single line like this:

1,2,3

Only the code column..

Someone has some idea of how it is possible to do this.. and whether it is possible?

postgresql query

2 answers

4


Use the function array_agg(expression):

Select array_agg(codigo) from tabela A

In the link you can see the postgresql documentation for more information.

  • 1

    Just to be documented, in case someone in Future looks for the same thing, I used a similar function..Select string_agg(cast(codigo as text),',') from tabelaA to return as a text, not an array (How array returns { } together). But your answer was of fundamental importance. Thank you very much

3

Structure:

CREATE TABLE public.foobar
(
    Codigo BIGINT,
    Descricao TEXT
);

Dice:

INSERT INTO public.foobar( Codigo, Descricao ) VALUES ( 1, 'Descricao 1' );
INSERT INTO public.foobar( Codigo, Descricao ) VALUES ( 2, 'Descricao 2' );
INSERT INTO public.foobar( Codigo, Descricao ) VALUES ( 3, 'Descricao 3' );

Consultation:

SELECT string_agg( Codigo::text, ',' ) FROM public.foobar;

Exit:

1,2,3

Browser other questions tagged postgresql query

You are not signed in. Login or sign up in order to post.