aggregation in postgres

Question

I have 3 tables in postgres containing information of the form:

<id, column1, column2, column3>

Now I intend to aggregate the 3 tables based on id, such that all the rows with the same id appear together. Is it feasible to do this in postgres with 3 tables. I also intend to store the clustered ids in a separate text file.

I know how to do clustering with 1 table..but I am not getting how to do clustering with 3 tables in postgres. Also I am not sure whether we can put the aggregated rows into a file. I googled with keywords like "postgres putting aggregate rows in text file", but I did not get an appropriate result.

Can someone please help me with this. Also I am a new to postgres, so pls excuse.

For example my input is:

 #@<id1> <moon> <diamter> <x>
 #@<id1> <moon> <closest_to> earth>

Now I want to do the following:

 #@<id1> <moon> <diameter> <x>, <moon> <closest_to> <earth>

That is I want to GROUPBY id and store the above grouped by results in a file. Is it feasible to do this in postgres. If yes..then how?

@Andomar I have edited my question for example input and output. Thanks for the reply — Maria Osmand
– Maria Osmand, Commented Mar 2, 2013 at 8:19
Please define "clustering". The term can mean a number of things. Also: Can any of your columns be NULL or empty ('')? — Erwin Brandstetter
– Erwin Brandstetter, Commented Mar 2, 2013 at 17:15

MatheusOl · Accepted Answer · 2013-03-02 10:44:02Z

3

If you want to get all the values from the three table, but doesn't know how many rows each one have for each id, you can UNION them:

    SELECT id, col1, col2, col3
    FROM tab1
    UNION
    SELECT id, col1, col2, col3
    FROM tab2
    UNION
    SELECT id, col1, col2, col3
    FROM tab3

With that, you can use string_agg to concatenate the results and make almost read to go to your file:

SELECT id, string_agg(col1 || ' ' || col2 || ' ' || col3, ', ')
FROM (
        SELECT id, col1, col2, col3
        FROM tab1
        UNION
        SELECT id, col1, col2, col3
        FROM tab2
        UNION
        SELECT id, col1, col2, col3
        FROM tab3
) AS tbls
GROUP BY id;

At least, you can copy the results to a file. There is, basically, two ways. The first is to do that from your application, which is good because it is easy to send to user (is there an user?). And the other is to use the COPY command, which has the issue that it will save the results on the database server, and the user postgres of the SO should have permission to write on desired path (or you can wrap the permissions with psql's \copy or your app).

The copy would be similar to:

COPY (
    SELECT '#@' || id || ' ' || string_agg(col1 || ' ' || col2 || ' ' || col3, ', ')
    FROM (
            SELECT id, col1, col2, col3
            FROM tab1
            UNION
            SELECT id, col1, col2, col3
            FROM tab2
            UNION
            SELECT id, col1, col2, col3
            FROM tab3
    ) AS tbls
    GROUP BY id
) TO '/tmp/yourfile.txt';

OBS: Notice that I used UNION, which will make repeated values between the tables to be suppressed. If you don't want that or you don't have repeated values (for sure) use UNION ALL instead (which has better performance also).

answered Mar 2, 2013 at 10:44

MatheusOl

12k3 gold badges33 silver badges29 bronze badges

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

2 Comments

Andomar Over a year ago

In the question the tables appear to have different column types (f.e. "<moon> <diamter> <x>" vs "<moon> <closest_to> earth>".) If that is so you cannot use a union without forcing equal types.

MatheusOl Over a year ago

@Andomar, She said nothing about the columns data type... So no way to be sure (should ask her). But if it is the problem, we can cast them to the same data-type (like text), which is ok to saving to a file.

Andomar · Accepted Answer · 2013-03-02 08:30:19Z

0

You could join the tables together:

select  coalesce(t1.id,t2.id,t3.id) as id
,       t1.col1
,       t1.col2
,       t2.col3
,       t3.col4
from    Table1 t1
full outer join
        Table2 t2
on      t1.id = t2.id
full outer join
        Table3 t3
on      t3.id = coalesce(t1.id, t2.id)

As for writing to a file, it looks like Postgres' COPY command could do that. You might have to store the query results in a table.

answered Mar 2, 2013 at 8:30

Andomar

239k55 gold badges387 silver badges412 bronze badges

Collectives™ on Stack Overflow

aggregation in postgres

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related