PostgreSQL – Multiple Results from Single Query into Different Columns

pivotpostgresqlpostgresql-9.4update

I have 2 tables that look something like this:

Table A:

CREATE TEMP TABLE table_a (
  ID_number_X text,
  ID_number_Y int
);
INSERT INTO table_a VALUES
  ('HT59452', 123),
  ('HT59453', 123),
  ('HT59454', 123),
  ('HT59455', 789),
  ('HT59456', 101);

Table B:

CREATE TEMP TABLE table_b (
  Column_1 text,
  ID_number_Y int,
  ID_number_X text
);
INSERT INTO table_b VALUES
  ('foo', 123, null),
  ('bar', 789, null),
  ('baz', 101, null);

I am running the following query to bring ID_number_X across to Table_B: 

UPDATE table_b AS B
SET id_number_x = A.id_number_x
FROM table_a AS A
WHERE B.id_number_y = A.id_number_y

This works fine, however, there are cases when there can be multiple results i.e. id_number_y can have more than one id_number_x associated with it.

How can I put those multiple results (when they occur) into additional columns?

So the result I want from the above example tables would be this:

Table B
column_1    id_number_y     id_number_x1   id_number_x2   id_number_x3
foo            123            HT59452        HT59453        HT59454
bar            789            HT59455
baz            101            HT59456

I think there could be up to 5 id_number_x for each id_number_y, so I can happily create the extra columns needed to store these.

Best Answer

A few things table_b's schema must be fixed. You can't have an update that updates variable columns, some of which may not even be created. Because you don't know how many matches you'll have, what you likely want here is an array type.

ALTER TABLE table_b
  DROP COLUMN ID_number_X,
  ADD COLUMN ID_number_X text[];

Now you have an array type of text[] on the table.

      Table "pg_temp_6.table_b"
   Column    |   Type    | Modifiers 
-------------+-----------+-----------
 column_1    | text      | 
 id_number_y | integer   | 
 id_number_x | integer[] |

And you can proceed creating UPDATE. Next, you need a simply query from which to do the update.

SELECT id_number_y, array_agg(id_number_x) AS id_number_x
FROM table_a
GROUP BY id_number_y;

 id_number_y |        id_number_x         
-------------+---------------------------
         123 | {HT59452,HT59453,HT59454}
         101 | {HT59456}
         789 | {HT59455}

With this you can wrap the query in the UPDATE and get what you want.

UPDATE table_b AS B
SET id_number_x = A.id_number_x
FROM (
    SELECT id_number_y, array_agg(id_number_x) AS id_number_x
    FROM table_a
    GROUP BY id_number_y
) AS A
WHERE B.id_number_y = A.id_number_y;

Now you have..

# TABLE TABLE_b;
 column_1 | id_number_y |        id_number_x        
----------+-------------+---------------------------
 foo      |         123 | {HT59452,HT59453,HT59454}
 bar      |         789 | {HT59455}
 baz      |         101 | {HT59456}

That said, this is rarely necessary and there are two other ways to do this which may better fit your needs,

  1. MATERIALIZED VIEW
  2. VIEW

Normally, this isn't needed because you simply use a JOIN.

Related Question