PostgreSQL – Insert Result of Join as an Array

postgresql

Here's what I have:

=# SELECT * FROM users;
 id |     name      
----+---------------
  1 | Paul Gilbert
  2 | Bob Dylan
  3 | Cliff Downing

=# SELECT * FROM regs;
 reg_id | user_id 
--------+---------
      1 |       1
     11 |       1
    111 |       1
      2 |       2
     22 |       2
      3 |       3

Here's what I want to obtain:

=# SELECT * FROM joined;
 id |     name      |       regs
----+---------------+-------------------
  1 | Paul Gilbert  | {1, 11, 111}
  2 | Bob Dylan     | {2, 22}
  3 | Cliff Downing | {3}

MWE:

-- create users table:
DROP TABLE users;
CREATE TABLE users(
   id    INT      PRIMARY KEY  NOT NULL,
   name  VARCHAR               NOT NULL
);
-- populate users table:
INSERT INTO users (id, name)
VALUES (1, 'Paul Gilbert'),
       (2, 'Bob Dylan'),
       (3, 'Cliff Downing');


-- reg table:
DROP TABLE regs;
CREATE TABLE regs(
   reg_id   INT  PRIMARY KEY  NOT NULL,
   user_id  INT               NOT NULL
);
-- populate reg table:
INSERT INTO regs (reg_id, user_id)
VALUES (1,   1),
       (11,  1),
       (111, 1),
       (2,   2),
       (22,  2),
       (3,   3);


-- joined table:
DROP TABLE joined;
CREATE TABLE joined(
    id    INT,
    name  VARCHAR,
    regs  INT[]
);

Here's my effort so far:

BEGIN;
  INSERT INTO joined (id, name)
       SELECT id, name
         FROM users;

  INSERT INTO joined (regs)
       SELECT ARRAY(
                   SELECT reg_id
                     FROM regs
               INNER JOIN joined
                       ON joined.id = regs.user_id
                   );
COMMIT;

It doesn't work:

 id |     name      |               regs
----+---------------+-----------------------------------
  1 | Paul Gilbert  | 
  2 | Bob Dylan     | 
  3 | Cliff Downing | 
    |               | {1,11,111,2,22,3}

Edit

Solution by a_horse_with_no_name works great.

Do you think there's a way to add yet another join in a single expression?

CREATE TABLE ldap.univer_users(
  first_name  VARCHAR,
  middle_name VARCHAR,
  last_name   VARCHAR,
  code        VARCHAR,
  doc_number  VARCHAR,
  reg_numbers VARCHAR[]
);

BEGIN;
    INSERT INTO ldap.univer_users (first_name, middle_name, last_name, code, reg_numbers)
    SELECT
        d."firstName", d."middleName", d."lastName", d.uid, array_agg(r."regNumber")
    FROM
        student_data d
        JOIN student_reg_numbers r ON d.uid = r.student_datum_uid
    GROUP BY d.uid;

    UPDATE ldap.univer_users
    SET doc_number = p.doc_number
    FROM (
            SELECT student_datum_uid, substring(number FROM '....$') AS doc_number
            FROM student_passports
            WHERE "passportType" IN ('Паспорт РФ', 'Паспорт иностранного гражданина')
         ) p
    WHERE code = p.student_datum_uid;
COMMIT;

This works with my data, but I'm curious is there a way to put it into a single insert statement.

Edit 2

Trying to unite all in a single expression:

INSERT INTO ldap.univer_users (first_name, middle_name, last_name, code, doc_number, reg_numbers)
SELECT
    d."firstName", d."middleName", d."lastName", d.uid, substring(p.number FROM '....$'), array_agg(r."regNumber")
FROM
    student_data d
    JOIN student_reg_numbers r ON d.uid = r.student_datum_uid
    JOIN student_passports   p ON d.uid = p.student_datum_uid
      AND p."passportType" IN ('Паспорт РФ', 'Паспорт иностранного гражданина')
GROUP BY d.uid;

Postgres says

column "p.number" must appear in the GROUP BY clause or be used in an aggregate function

Best Answer

You need to use a single insert statement:

INSERT INTO joined (id, name, regs)
SELECT u.id, u.name, array_agg(reg_id)
FROM users u
  JOIN regs r ON r.user_id = u.id
GROUP BY u.id, u.name;

Your two step approach requires an update as the second step, not an insert:

INSERT INTO joined (id, name)
SELECT id, name
FROM users;

update joined 
   set regs = t.regs
from (  
  select user_id, array_agg(reg_id) as regs
  from regs
  group by user_id
) t
where t.user_id = joined.id;