1

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
  • ask another question... – Evan Carroll Dec 27 '16 at 20:00
4

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;
  • Thanks. It works great. Turns out I need to make yet another join (see edit). Can it be done with a single insert statement? – Adobe Dec 27 '16 at 18:15
  • @Adobe: just join student_passports to the first query: JOIN student_passports p ON p.student_datum_uid = d.uid AND p."passportType" IN (...) – a_horse_with_no_name Dec 27 '16 at 19:59
  • Adding second join doesn't seem to be working (please take a look at edit 3). Postgres says I need to add one more group by. – Adobe Dec 28 '16 at 16:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.