Question 277461 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Selecting from multiple tables, and assign results to new columns

Is it advised or even possible to select from multiple tables, and assign the results from some of the tables as new columns in the query result?

Lets say I have 3 tables: cars, records, maintenance

If given a cars id as ‘1234’, I want to select * from cars with that id and create two new columns (only in result) called race_records and maintenance_history which will be an arrays of all matching rows from records and maintenance

Or do you typically make multiple separate queries in this case?

Tags:

2

Answers


  1. 0

    You can use string_agg() to perform aggregation of row values in single column.

    May below is the example could help you.

    SELECT string_agg(name::text, ',') as new_column FROM cars;

    Output:

    Swift,Creta,Scorpio,Alto
    Login or Signup to reply.
  2. 0

    If you attempt a triple join, you’ll multiply your records rows by your maintenance rows (a car with 2 records and 2 maintenances spawns 4 rows, each with a combination of the two). You can use a scalar subquery to aggregate in – technically, these aren’t separate queries. Demo:

    select c.*, 
      (select array_agg(r) from records as r     where c.id=r.car_id) as records,
      (select array_agg(m) from maintenance as m where c.id=m.car_id) as maintenance
from cars as c;
    id name records maintenance
    1234 my car {"(1,1234,record1)","(2,1234,record2)"} {"(1,1234,maintenance1)","(2,1234,maintenance2)"}

    Array_agg() results in adequately typed arrays of all matching rows that let you subscript by index and address by field (column) name:

    select id,
       name, 
       pg_typeof(records), 
       records[1].title, 
       maintenance[2].description 
from(select c.*, 
            (select array_agg(r) from records as r where c.id=r.car_id) as records,
            (select array_agg(m) from maintenance as m where c.id=m.car_id) as maintenance
     from cars as c
) as subquery;
    id name pg_typeof title description
    1234 my car records[] record1 maintenance2

    Depending on what you do with them later, you might consider jsonb_object_agg() instead.

    Is it advised or even possible to select from multiple tables, and assign the results from some of the tables as new columns in the query result?

    Creating a new column based on a some sort of transformation of input values and giving it a new alias is a pretty common thing to do.

    do you typically make multiple separate queries in this case?

    SQL is declarative. The main idea is that you tell the system what exactly it is that you want, and it’s the system that decides how exactly to deliver it – an attempt to split the operation into "steps" goes against that idea. Ideally, if you can state your business with a single statement, you do it: the more context you give to the planner about your goal, the better it can select methods to achieve it, and optimise the operation.

    You could do it step-by-step, imperatively, but at best you’d end up manually laying out the same logic the planner can come up with on its own – you can play around with explain analyze and see the underlying mechanics of how your data gets processed and delivered. You can of course try and come up with faster/cheaper methods than that, in which case you’re encouraged to share them and see if they can get built into PostgreSQL.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search